myportal/soap_service/server_newpatienttoapprove.php

   1 <?php
   2 /**
   3  * soap_service/server_newpatienttoapprove.php Server side code to unapproved patients.
   4  *
   5  * Functions to handle unapproved patients.
   6  *
   7  * Copyright (C) 2013 Z&H Consultancy Services Private Limited <sam@zhservices.com>
   8  *
   9  * LICENSE: This program is free software; you can redistribute it and/or
  10  * modify it under the terms of the GNU General Public License
  11  * as published by the Free Software Foundation; either version 3
  12  * of the License, or (at your option) any later version.
  13  * This program is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  16  * GNU General Public License for more details.
  17  * You should have received a copy of the GNU General Public License
  18  * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
  19  *
  20  * @package OpenEMR
  21  * @author  Eldho Chacko <eldho@zhservices.com>
  22  * @author  Jacob T Paul <jacob@zhservices.com>
  23  * @author  Ajil P M <ajilpm@zhservices.com>
  24  * @link    http://www.open-emr.org
  25  */
  26
  27
  28
  29
  30 class newpatienttoapprove
  31 {
  32
  33
  34 //this will return the query string along with the parameter array, according to the case case.
  35 //actual execution is done in the select_query function in Server_side
  36
  37     public function query_formation($data)
  38     {
  39         global $pid,$auditmasterid;
  40         switch ($data[0]) {
  41             case 'A1':
  42             //Select list of encounters for the patients
  43                 $query="select f.id, f.date, f.pid, f.encounter, f.stmt_count, f.last_stmt_date, f.facility_id,f.billing_facility, " .
  44                   "p.fname, p.mname, p.lname, p.street, p.city, p.state, p.postal_code " .
  45                       " ,u.fname as dfname, u.mname as dmname, u.lname as dlname".
  46                 " from ((form_encounter AS f, patient_data AS p) " .
  47                       " left join users as u on f.provider_id =u.id) ".
  48                       " left join facility as fa on fa.id =f.billing_facility ".
  49                   "WHERE ( f.pid=? ) AND " .
  50                   "p.pid = f.pid " .
  51                   "ORDER BY f.pid,f.billing_facility, f.date desc, f.encounter desc";
  52                 return array($query,array($pid));
  53             break;
  54             //Select list of providers
  55             case 'A2':
  56                 $query="Select distinct u.fname, u.mname, u.lname".
  57                 " from (form_encounter AS f, billing AS b) " .
  58                       " left join users as u on f.provider_id =u.id ".
  59                   " WHERE f.pid = b.pid and  f.encounter = b.encounter " .
  60                       " and f.encounter=? and f.pid=? ".
  61                   " ORDER BY u.fname, u.lname";
  62                 array_push($data[1], $pid);
  63                 return array($query,array($data[1]));
  64             break;
  65             //Select list of encounters for the patients
  66             case 'A3':
  67             //ledger
  68                 $where = '';
  69                 $wherearray=array();
  70                 foreach ($data[1][0] as $k => $v) {
  71                     $where .= " OR f.id = ?";
  72                     $wherearray[]=$v;
  73                 }
  74
  75                 $where = substr($where, 4);
  76                 if (!$where) {
  77                     $where='?';
  78                     $wherearray[]=0;
  79                 }
  80
  81                 $wherearray[]=$pid;
  82                    $query= "Select f.id, f.date, f.pid, f.encounter, f.stmt_count, f.last_stmt_date, f.facility_id,f.billing_facility, " .
  83                   "p.fname, p.mname, p.lname, p.street, p.city, p.state, p.postal_code " .
  84                       " ,u.fname as dfname, u.mname as dmname, u.lname as dlname".
  85                 " from ((form_encounter AS f, patient_data AS p) " .
  86                       " left join users as u on f.provider_id =u.id) ".
  87                       " left join facility as fa on fa.id =f.billing_facility ".
  88                   "WHERE ( $where) AND " .
  89                   "p.pid = f.pid  and f.pid=?" .
  90                   "ORDER BY f.pid,f.billing_facility, f.date desc, f.encounter desc";
  91                 return array($query,$wherearray);
  92             break;
  93
  94             case 'A4':
  95                         //Select encounter of the patient
  96                 $query="select * from form_encounter where pid=? limit 1 ";
  97                 return array($query,array($pid));
  98             break;
  99
 100             case 'A5':
 101             //Select encounters of the patient
 102                         $enc_set_array=array();
 103                 $enc_set_array[]=$pid;
 104                 if ($data[1][1]=='' && $data[1][2]>0) {
 105                     $enc_set= " and encounter=? " ;
 106                     $enc_set_array[]=$data[1][2];
 107                 }
 108
 109                 $provider="";
 110                 $provider  =add_escape_custom($data[1][0]);
 111                 $query="select fe.id,fe.pid,encounter,date_format(fe.date,'%Y-%m-%d')
 112             as date,concat(pd.lname,' ',pd.fname) as patname,concat(u.lname,', ',u.fname)
 113             as provname,".$provider." from form_encounter fe left outer join users u
 114              on u.id =fe.".$provider." join patient_data pd on pd.pid=fe.pid where
 115              fe.pid=?".  $enc_set ." order by fe.date desc";
 116                 return array($query, $enc_set_array);
 117             break;
 118
 119             case 'A6':
 120                         //Select encounter and copay of the patient
 121                 $enc_set_array=array();
 122                 $enc_set_array[]=$pid;
 123                 if ($data[1][0]=='' && $data[1][1]>0) {
 124                     $enc_set= " and encounter=? ";
 125                     $enc_set_array[]=$data[1][1];
 126                 }
 127
 128                 $query="select encounter,sum(fee) as copay ".
 129                 " from billing where code_type='copay' and pid=? $enc_set group by encounter";
 130                 return array($query,$enc_set_array);
 131             break;
 132             //DEtails of CPT, Diagnosis etc of an encounter
 133             case 'A7':
 134                 $enc_set_array=array();
 135                 $enc_set_array[]=$pid;
 136                 if ($data[1][0]=='' && $data[1][1]>0) {
 137                     $enc_set= " and encounter=? ";
 138                     $enc_set_array[]=$data[1][1];
 139                 }
 140
 141                 $query="select concat(encounter,code,modifier) as ecm,encounter,code,
 142             modifier,units,fee,code_text,justify from billing where activity=1 and fee>0 and code_type not in('ICD9','copay') and pid=? $enc_set";
 143                 return array($query,$enc_set_array);
 144             break;
 145             //Payment details  of an encounter
 146             case 'A8':
 147                 $enc_set_array=array();
 148                 $enc_set_array[]=$pid;
 149                 if ($data[1][0]=='' && $data[1][1]>0) {
 150                     $enc_set= " and encounter=? ";
 151                     $enc_set_array[]=$data[1][1];
 152                 }
 153
 154                 $query="select concat(encounter,code,modifier) as pecm,encounter,code,
 155             modifier,pay_amount,adj_amount,payer_type,post_time,account_code,
 156             follow_up_note,memo,date_format(post_time,'%Y-%m-%d') as dtfrom from ar_activity where pid=? $enc_set";
 157                 return array($query,$enc_set_array);
 158             break;
 159             case 'A9':
 160                         //Payment
 161                 $query = "SELECT sum(pay_total)  as pay_total FROM ar_session WHERE patient_id=? AND adjustment_code=?";
 162                 return array($query,array($pid,'pre_payment'));
 163             break;
 164             case 'A10':
 165                         //Payment
 166                 $query = "SELECT sum(pay_amount)  as pay_amount FROM ar_session,ar_activity WHERE patient_id=? AND adjustment_code=?
 167                           AND pid=? AND ar_session.session_id=ar_activity.session_id  and pay_amount>0";
 168                 return array($query,array($pid,'pre_payment',$pid));
 169             break;
 170             case 'A11':
 171                         //Payment
 172                 $query = "SELECT sum(pay_total)  as pay_total FROM ar_session WHERE patient_id=? AND adjustment_code!=?";
 173                 return array($query,array($pid,'pre_payment'));
 174             break;
 175             case 'A12':
 176                         //Payment
 177                 $query = "SELECT sum(pay_amount)  as pay_amount FROM ar_session,ar_activity WHERE patient_id=? AND adjustment_code!=?
 178                           AND pid=? AND ar_session.session_id=ar_activity.session_id  and pay_amount>0";
 179                 return array($query,array($pid,'pre_payment',$pid));
 180             break;
 181
 182         // Entries pending  for approval for Existing Patient and New Patient.
 183             case 'U4':
 184                 $query=  "select * from audit_master where id=? and approval_status='1' and (type='1' or type='2')";
 185                 return array($query,array($auditmasterid));
 186             break;
 187             // Entries pending  for approval for  documents only (no demo change).
 188             case 'U5':
 189                 $query = " select * from audit_master where pid=? and approval_status='1' and  type='3' ";
 190                 return array($query,$data[1]);
 191             break;
 192
 193             case 'P1':
 194                         //Pid
 195                 $query= "select '0' AS pid";
 196                 return array($query);
 197             break;
 198
 199             //for building patient Demo
 200             case 'P2':
 201                 $query="select * from  layout_options " .
 202                 "WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
 203                 "ORDER BY group_name, seq";
 204                 return array($query);
 205             break;
 206
 207             //for building patient Demo   Date of Birth
 208             case 'P3':
 209                         $query="SELECT * FROM audit_details WHERE audit_master_id = ? AND table_name = 'patient_data'";
 210                         $query_res = sqlStatement($query, array($auditmasterid));
 211                 while ($result = sqlFetchArray($query_res)) {
 212                     if ($result['field_name'] == 'DOB') {
 213                           $dob_res = sqlQuery("SELECT DATE_FORMAT(?,'%Y-%m-%d') as DOB_YMD", array($result['field_value']));
 214                           $res['DOB_YMD'] = $dob_res['DOB_YMD'];
 215                     }
 216
 217                     $res[$result['field_name']] = $result['field_value'];
 218                 }
 219                 return array($res,'result');
 220             break;
 221
 222            //for building patient Demo   Employer Data
 223             case 'P4':
 224                         $query="SELECT * FROM audit_details WHERE audit_master_id = ? AND table_name = 'employer_data'";
 225                         $query_res = sqlStatement($query, array($auditmasterid));
 226                 while ($result = sqlFetchArray($query_res)) {
 227                     $res[$result['field_name']] = $result['field_value'];
 228                 }
 229                 return array($res,'result');
 230             break;
 231
 232             //for building patient Demo   Insurance company details for Patient
 233             case 'P5':
 234                         $query="SELECT * FROM audit_details WHERE audit_master_id = ? AND table_name = 'insurance_data' AND entry_identification = ?";
 235                         array_unshift($data[1], $auditmasterid);
 236                         $query_res = sqlStatement($query, $data[1]);
 237                 while ($result = sqlFetchArray($query_res)) {
 238                     $res[$result['field_name']] = $result['field_value'];
 239                 }
 240                 return array($res,'result');
 241             break;
 242
 243             // Entries pending  for approval demo and documents.
 244             case 'P6':
 245                 $query=" select * from audit_master as am,audit_details as ad WHERE am.id=ad.audit_master_id and ad.audit_master_id=? and am.approval_status='1'
 246               and  (am.type='1' or am.type='2' or am.type='3') order by ad.id";
 247                 return array($query,array($auditmasterid));
 248             break;
 249             // Demo building from layout options.
 250
 251             case 'P7':
 252                 $query=" select * from layout_options WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
 253                 "ORDER BY group_name, seq";
 254
 255                 return array($query);
 256             break;
 257
 258             //Global specific application for building demo.
 259             case 'P8':
 260                 $query="select * from  globals where gl_name ='specific_application' ";
 261                 return array($query);
 262             break;
 263
 264             //Global omit employers for building demo.
 265             case 'P9':
 266                 $query=" select * from globals where gl_name ='omit_employers' ";
 267                 return array($query);
 268             break;
 269             //details of COpay and Fees
 270             case 'P10':
 271                 array_push($data[1], $pid);
 272                 $query= "select date, code_type, code, modifier, code_text, fee , units, justify  from billing WHERE  encounter =? AND pid = ? AND " .
 273                 " activity = 1 AND fee != 0.00 ORDER BY  fee desc,code,modifier";
 274                 return array($query,$data[1]);
 275             break;
 276
 277             case 'P11':
 278                 $query = "select code_text from codes WHERE  code = ? ";
 279                 return array($query,$data[1]);
 280             break;
 281             //Details of drug sales
 282             case 'P12':
 283                 array_push($data[1], $pid);
 284                 $query = "select s.drug_id, s.sale_date, s.fee, s.quantity from drug_sales AS s " .
 285                 "WHERE  s.encounter = ? and s.pid = ? AND s.fee != 0 " .
 286                 "ORDER BY s.sale_id";
 287                 return array($query,$data[1]);
 288             break;
 289             //Details of Payments
 290             case 'P14':
 291                 array_push($data[1], $pid);
 292                     $query = "Select a.code, a.modifier, a.memo, a.payer_type, a.adj_amount, a.pay_amount, " .
 293                 "a.post_time, a.session_id, a.sequence_no,a.follow_up, a.follow_up_note, " .
 294                 "s.payer_id, s.reference, s.check_date, s.deposit_date " .
 295                 ",i.name from ar_activity AS a " .
 296                 "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 297                 "LEFT OUTER JOIN insurance_companies AS i ON i.id = s.payer_id " .
 298                 "WHERE  a.encounter = ? and a.pid = ? " .
 299                 "ORDER BY s.check_date, a.sequence_no";
 300                 return array($query,$data[1]);
 301             break;
 302             //Address of Billing Facility
 303             case 'P15':
 304                 $query = "SELECT f.name,f.street,f.city,f.state,f.postal_code,f.phone from facility f " .
 305                 " where  id=?";
 306                 return array($query,$data[1]);
 307             break;
 308             //Encounter status primary,secondary Etc
 309             case 'P16':
 310                 array_push($data[1], $pid);
 311                 $query = "select last_level_closed from form_encounter where encounter= ? and pid =? ";
 312                 return array($query,$data[1]);
 313             break;
 314                         //Insurance types
 315             case 'P17':
 316                 $query = "select COUNT( DISTINCT TYPE ) NumberOfInsurance from insurance_data where pid =? and provider>0 ";
 317                 return array($query,array($pid));
 318             break;
 319                         //Encounter date
 320             case 'P19':
 321                 $query = "select  date,encounter from form_encounter where pid =? ORDER BY encounter";
 322                 return array($query,array($pid));
 323             break;
 324                         //Duplicate existance
 325             case 'P20':
 326                 if ($pid) {
 327                       $string_query=" and pid !=?";
 328                 }
 329
 330                 if ($string_query) {
 331                     $x=array($data[1][0],$pid);
 332                 } else {
 333                     $x=array($data[1][0]);
 334                 }
 335
 336                 $query="select count(*) AS count from patient_data where pubpid = ? $string_query";
 337                 return array($query,$x);
 338             break;
 339
 340             //getting DOB and SSN for verifying the duplicate patient existance
 341             case 'P21':
 342                 if ($pid) {
 343                       $string_query=" and pid !=?";
 344                 }
 345
 346                 if ($string_query) {
 347                     $x=array($data[1][0],$pid);
 348                 } else {
 349                     $x=array($data[1][0]);
 350                 }
 351
 352                 $query="select  ss,DOB  from patient_data where DOB=? $string_query  ";
 353                 return array($query,$x);
 354             break;
 355
 356             //master data for calendar from Globals
 357             case 'B1':
 358             //patient appointment
 359                 if ($data[1][0]=='calendar_interval'||$data[1][0]=='schedule_start'||$data[1][0]=='schedule_end') {
 360                     $query="select gl_value from globals where gl_name=?";
 361                     return array($query,$data[1]);
 362                 } else {
 363                     return 0;
 364                 }
 365                 break;
 366
 367             case 'B4':
 368              //Check whether an entry exist in the form Encounter to decide whether patient is an existing patient.
 369                 $query="select COUNT(*) AS cnt from form_encounter WHERE pid=?";
 370                 return array($query,array($pid));
 371             break;
 372
 373             case 'B5':
 374            //Existing appointments for a patient
 375                 array_unshift($data[1], $pid);
 376                 $query="select pc_eid,pc_eventDate,pc_startTime,pc_endTime,fname,lname,name,pc_apptstatus from openemr_postcalendar_events AS c,
 377             users AS u,facility AS f WHERE pc_pid=? AND pc_aid=u.id AND pc_facility=f.id AND pc_apptstatus!=? order by pc_eventDate desc";
 378                 return array($query,$data[1]);
 379             break;
 380
 381             case 'B6':
 382             //Appointments pending for approval
 383                 array_push($data[1], $pid);
 384                 $query="select am.id,am.approval_status,ad.audit_master_id,ad.field_name,ad.field_value,u.fname,u.lname,f.name from audit_master AS am,
 385             audit_details AS ad LEFT JOIN users AS u ON ad.field_value=u.id AND ad.field_name=? LEFT JOIN facility AS f ON ad.field_value=f.id AND
 386             ad.field_name=? WHERE am.pid=? AND am.id=ad.audit_master_id AND am.type='10' AND am.approval_status NOT IN ('2','4')
 387             ORDER BY approval_status, am.id desc,ad.id desc";
 388                 return array($query,$data[1]);
 389             break;
 390
 391             case 'B7':
 392             //patient appointment history
 393                 array_unshift($data[1], $pid);
 394                 $query="select pc_eid,pc_eventDate,pc_startTime,pc_endTime,fname,lname,name,pc_apptstatus from openemr_postcalendar_events AS c,
 395             users AS u,facility AS f WHERE pc_pid=? AND pc_aid=u.id AND pc_facility=f.id AND pc_apptstatus=? order by pc_eventDate desc";
 396                 return array($query,$data[1]);
 397             break;
 398
 399             case 'B8':
 400             //List of Service Facility
 401                 $query="select * from facility where service_location != 0 and id in (".add_escape_custom($data[1][0]).") order by name";
 402                 return array($query);
 403             break;
 404
 405             case 'B9':
 406             //Providers list
 407                 $query="select id, lname, fname from users WHERE authorized = 1 AND username != '' AND username NOT LIKE '%Admin%' AND active = 1
 408             AND ( info IS NULL OR info NOT LIKE '%Inactive%' ) ORDER BY lname, fname";
 409                 return array($query);
 410             break;
 411
 412             case 'B10':
 413            //Calendar default visit time for visit category. value for Admin--->others-->calendar
 414                 $query="select pc_duration from openemr_postcalendar_categories WHERE pc_catid = ?";
 415                 return array($query,$data[1]);
 416             break;
 417
 418             case 'B11'://This was previously a semicolon, assumed to be a bug and replaced with a colon
 419             //patient appointment
 420                 $query="select pc_eventDate, pc_endDate, pc_startTime, pc_duration, pc_recurrtype, pc_recurrspec, pc_alldayevent, pc_catid,
 421                 pc_prefcatid from openemr_postcalendar_events WHERE pc_aid = ? AND ((pc_endDate >= ? AND pc_eventDate < ?) OR
 422                 (pc_endDate = '0000-00-00' AND pc_eventDate >= ? AND pc_eventDate < ?)) AND pc_facility = ?";
 423                 return array($query,$data[1]);
 424             break;
 425
 426             case 'B12':
 427             //Appointments pending for approval
 428                 $query="select * from audit_master WHERE pid =? AND type='10'";
 429                 return array($query,array($pid));
 430             break;
 431
 432             case 'B13':
 433             //patient appointment
 434                 $query="select * from facility where service_location != 0 order by name";
 435                 return array($query);
 436             break;
 437
 438             //C series for mailbox.
 439             case 'C1':
 440                     $query = "SELECT username, fname, lname FROM users WHERE username != '' AND active = 1 AND ( info IS NULL OR info NOT LIKE
 441                     '%Inactive%' ) ORDER BY lname, fname";
 442                 return array($query);
 443                             break;
 444
 445             case 'C2':
 446                     $query = "SELECT option_id, title FROM list_options WHERE list_id = ? AND activity = 1 ORDER BY seq";
 447                 return array($query,$data[1]);
 448                             break;
 449
 450             //D series for patient.
 451             case 'D1':
 452                     $query = "SELECT forms.encounter, forms.form_id, forms.id, forms.form_name, forms.formdir,forms.date AS fdate,
 453                     form_encounter.date ,form_encounter.reason FROM forms LEFT OUTER JOIN form_encounter ON  forms.pid=form_encounter.pid
 454                     WHERE forms.pid = ? AND forms.deleted=0 AND forms.formdir<>? GROUP BY id ORDER BY forms.encounter,fdate  ASC";
 455                     array_unshift($data[1], $pid);
 456                 return array($query,$data[1]);
 457                             break;
 458
 459             case 'D2':
 460                     $query = "SELECT name FROM registry ORDER BY priority";
 461                 return array($query);
 462                             break;
 463
 464             case 'D3':
 465                     $query = "select * from lists WHERE pid =? ORDER BY type, begdate";
 466                 return array($query,array($pid));
 467                             break;
 468
 469             case 'D4':
 470                     $query = "select encounter from issue_encounter WHERE pid = ? AND list_id = ?";
 471                             array_unshift($data[1], $pid);
 472                 return array($query,$data[1]);
 473                             break;
 474
 475             //G series for form menu inc
 476             case 'G1':
 477                     $query = "SELECT * FROM `documents_legal_categories` where dlc_category_name=? and dlc_category_type=2";
 478                 return array($query,$data[1]);
 479                             break;
 480
 481             case 'G2':
 482                     $query = "SELECT * FROM documents_legal_master AS dlm WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND
 483                     dlm_effective_date<>? AND dlm_upload_type = '0' AND dlm_document_id NOT IN (SELECT DISTINCT (ad2.field_value) FROM
 484                                                                                 audit_details ad JOIN audit_details ad1 ON ad1.table_name = 'patient_access_offsite' AND ad1.field_name = 'portal_pwd'
 485                                                                                 AND ad1.audit_master_id = ad.audit_master_id JOIN audit_details ad2 ON ad2.table_name = 'documents_legal_detail' AND
 486                                                                                 ad2.field_name = 'dld_master_docid' JOIN audit_details ad3 ON ad3.table_name = 'patient_access_offsite' AND
 487                                                                                 ad3.field_name = 'portal_username' AND ad3.audit_master_id = ad2.audit_master_id WHERE ad.audit_master_id = ? AND
 488                                                                                 ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username' AND ad3.field_value = ad.field_value)";
 489                             array_push($data[1], $auditmasterid);
 490                 return array($query,$data[1]);
 491                             break;
 492
 493             case 'G3':
 494                     $query = "SELECT *, ad5.field_value AS dld_filename FROM documents_legal_master AS dlm JOIN audit_details ad ON
 495                                                                                 ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username' JOIN audit_details ad1 ON
 496                                                                                 ad1.table_name = 'patient_access_offsite' AND ad1.field_name = 'portal_pwd' AND ad1.audit_master_id = ad.audit_master_id
 497                                                                                 JOIN audit_details ad2 ON ad2.table_name = 'patient_access_offsite' AND ad2.field_name = 'portal_username' AND
 498                                                                                 ad2.field_value = ad.field_value JOIN audit_details ad3 ON ad3.table_name = 'documents_legal_detail' AND
 499                                                                                 ad3.field_name = 'dld_master_docid' AND ad3.field_value = dlm.dlm_document_id AND ad3.audit_master_id = ad2.audit_master_id
 500                                                                                 JOIN audit_details ad4 ON ad4.table_name = 'documents_legal_detail' AND ad4.field_name = 'dld_signed' AND
 501                                                                                 ad4.audit_master_id = ad3.audit_master_id JOIN audit_details ad5 ON ad5.table_name = 'documents_legal_detail'
 502                                                                                 AND     ad5.field_name = 'dld_filename' AND ad5.audit_master_id = ad3.audit_master_id WHERE dlm_subcategory <> ?
 503                                                                                 AND dlm_effective_date <= NOW() AND dlm_effective_date <> ? AND ad4.field_value = ? AND ad.audit_master_id = ?
 504                                                                                 GROUP BY dlm.dlm_document_id ORDER BY dlm_effective_date DESC";
 505                             array_push($data[1], $auditmasterid);
 506                 return array($query,$data[1]);
 507                             break;
 508
 509             case 'G4':
 510                     $query = "SELECT * FROM documents_legal_master AS dlm JOIN documents_legal_detail as dld ON dlm_document_id=dld_master_docid
 511                     JOIN form_encounter as fe ON encounter=dld_encounter WHERE dlm_subcategory = ? AND dlm_effective_date <= now() AND
 512                     dlm_effective_date<>? AND dld_id IS NOT NULL AND dld_signed=? AND dld_signing_person=? AND dld_pid=?
 513                     ORDER BY dlm_effective_date DESC";
 514                             array_push($data[1], $pid);
 515                 return array($query,$data[1]);
 516                             break;
 517
 518             case 'G5':
 519                     $query = "SELECT * FROM documents_legal_master AS dlm JOIN documents_legal_detail as dld ON dlm_document_id=dld_master_docid
 520                     JOIN form_encounter as fe ON encounter=dld_encounter WHERE dlm_subcategory = ? and dlm_effective_date <= now() AND
 521                     dlm_effective_date<>? AND dld_id IS NOT NULL AND dld_filename != '' AND dld_pid=? GROUP BY dld_encounter,dlm_document_id
 522                     ORDER BY dld_id DESC";
 523                             array_push($data[1], $pid);
 524                 return array($query,$data[1]);
 525                             break;
 526             case 'G6':
 527                     $query = "SELECT * FROM documents_legal_master AS dlm LEFT OUTER JOIN documents_legal_detail as dld ON
 528                     dlm_document_id=dld_master_docid WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND dlm_effective_date<>?
 529                     AND dld_id IS NOT NULL AND (dld_signed = ? OR dlm_upload_type = '1') AND dld_pid=? ORDER BY dlm_effective_date DESC";
 530                             array_push($data[1], $pid);
 531                 return array($query,$data[1]);
 532                             break;
 533
 534             case 'F1':
 535             //Patient details .
 536                         $query="SELECT * FROM audit_details WHERE audit_master_id = ? AND table_name = 'patient_data'";
 537                         $query_res = sqlStatement($query, array($auditmasterid));
 538                 while ($result = sqlFetchArray($query_res)) {
 539                     $res[$result['field_name']] = $result['field_value'];
 540                 }
 541                 return array($res,'result');
 542             break;
 543
 544             case 'F2':
 545             //PDF forms detail selected
 546                 $query="select * from documents_legal_master where dlm_document_id=?";
 547                 return array($query,$data[1]);
 548             break;
 549
 550             case 'F3':
 551             //signing
 552                 array_unshift($data[1], $pid);
 553                 $query="select * from documents_legal_detail where dld_pid = ? and dld_signed='3' and dld_master_docid = ?";
 554                 return array($query,$data[1]);
 555             break;
 556
 557             case 'F6':
 558             //signing
 559                 $query="select * from documents_legal_master where dlm_document_id=?";
 560                 return array($query,$data[1]);
 561             break;
 562
 563             case 'F8':
 564             // Entries to be approved demo  for new patient, existing patient and only documents
 565                 $query="select * from audit_master where id=? and approval_status='1' and (type='1' or type='2' or type='3')";
 566                 return array($query,array($auditmasterid));
 567             break;
 568
 569             case 'F9':
 570             //signing
 571                 $query="select * from documents_legal_master WHERE dlm_document_id=?";
 572                 return array($query,$data[1]);
 573             break;
 574
 575             case 'F10':
 576             //Documents ready to be signed and documents unsigned
 577                 array_unshift($data[1], $pid);
 578                 $query="select * from documents_legal_detail where dld_pid=? and (dld_signed='2' or dld_signed='0') and dld_master_docid=?";
 579                 return array($query,$data[1]);
 580             break;
 581
 582
 583             case 'F12':
 584             //Selection from master document for showing to patient
 585                 $query="select * from documents_legal_master WHERE dlm_document_name=?";
 586                 return array($query,$data[1]);
 587             break;
 588
 589             case 'J1':
 590                         //First name of patient
 591                 $query = "SELECT field_value as fname FROM audit_details WHERE audit_master_id=? AND table_name='patient_data' AND field_name = 'fname'";
 592                 return array($query,array($auditmasterid));
 593             break;
 594             //Checking whether a new patient entry is pending in the audit master
 595             case 'J2':
 596                 $query = "SELECT pid FROM audit_master WHERE approval_status=1 and type=1 and pid=?";
 597                 return array($query,array($pid));
 598             break;
 599
 600             case 'payment_settings':
 601                 $query = "SELECT login_id,transaction_key,md5 FROM payment_gateway_details WHERE service_name=?";
 602                 return array($query,$data[1]);
 603             break;
 604
 605             case 'authorizenet_id':
 606                 $query = "SELECT authorize_net_id FROM patient_access_offsite WHERE pid=?";
 607                 return array($query,array($pid));
 608             break;
 609         }
 610     }
 611 }