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