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