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 //STOP FAKE REGISTER GLOBALS
  28 $fake_register_globals=false;
  29 //
  30
  31
  32 class newpatienttoapprove {
  33
  34
  35 //this will return the query string along with the parameter array, according to the case case.
  36 //actual execution is done in the select_query function in Server_side
  37
  38   public function query_formation($data){
  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             {
  72             $where .= " OR f.id = ?";
  73             $wherearray[]=$v;
  74             }
  75             $where = substr($where, 4);
  76             if(!$where)
  77             {
  78             $where='?';
  79             $wherearray[]=0;
  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             {
 106             $enc_set= " and encounter=? " ;
 107             $enc_set_array[]=$data[1][2];
 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             {
 125             $enc_set= " and encounter=? ";
 126             $enc_set_array[]=$data[1][1];
 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             {
 138             $enc_set= " and encounter=? ";
 139             $enc_set_array[]=$data[1][1];
 140
 141             }
 142             $query="select concat(encounter,code,modifier) as ecm,encounter,code,
 143             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";
 144             return array($query,$enc_set_array);
 145             break;
 146             //Payment details  of an encounter
 147             case 'A8':
 148             $enc_set_array=array();
 149             $enc_set_array[]=$pid;
 150             if($data[1][0]=='' && $data[1][1]>0)
 151             {
 152             $enc_set= " and encounter=? ";
 153             $enc_set_array[]=$data[1][1];
 154             }
 155             $query="select concat(encounter,code,modifier) as pecm,encounter,code,
 156             modifier,pay_amount,adj_amount,payer_type,post_time,account_code,
 157             follow_up_note,memo,date_format(post_time,'%Y-%m-%d') as dtfrom from ar_activity where pid=? $enc_set";
 158             return array($query,$enc_set_array);
 159             break;
 160             case 'A9':
 161                                                 //Payment
 162                 $query = "SELECT sum(pay_total)  as pay_total FROM ar_session WHERE patient_id=? AND adjustment_code=?";
 163                 return array($query,array($pid,'pre_payment'));
 164             break;
 165             case 'A10':
 166                                                 //Payment
 167                 $query = "SELECT sum(pay_amount)  as pay_amount FROM ar_session,ar_activity WHERE patient_id=? AND adjustment_code=?
 168                           AND pid=? AND ar_session.session_id=ar_activity.session_id  and pay_amount>0";
 169                 return array($query,array($pid,'pre_payment',$pid));
 170             break;
 171             case 'A11':
 172                                                 //Payment
 173                 $query = "SELECT sum(pay_total)  as pay_total FROM ar_session WHERE patient_id=? AND adjustment_code!=?";
 174                 return array($query,array($pid,'pre_payment'));
 175             break;
 176             case 'A12':
 177                                                 //Payment
 178                 $query = "SELECT sum(pay_amount)  as pay_amount FROM ar_session,ar_activity WHERE patient_id=? AND adjustment_code!=?
 179                           AND pid=? AND ar_session.session_id=ar_activity.session_id  and pay_amount>0";
 180                 return array($query,array($pid,'pre_payment',$pid));
 181             break;
 182
 183             // Entries pending  for approval for Existing Patient and New Patient.
 184             case 'U4':
 185             $query=  "select * from audit_master where id=? and approval_status='1' and (type='1' or type='2')";
 186             return array($query,array($auditmasterid));
 187             break;
 188             // Entries pending  for approval for  documents only (no demo change).
 189             case 'U5':
 190             $query = " select * from audit_master where pid=? and approval_status='1' and  type='3' ";
 191             return array($query,$data[1]);
 192             break;
 193
 194             case 'P1':
 195                                                 //Pid
 196             $query= "select '0' AS pid";
 197             return array($query);
 198             break;
 199
 200             //for building patient Demo
 201             case 'P2':
 202             $query="select * from  layout_options " .
 203               "WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
 204               "ORDER BY group_name, seq";
 205             return array($query);
 206             break;
 207
 208             //for building patient Demo   Date of Birth
 209             case 'P3':
 210                                                 $query="SELECT * FROM audit_details WHERE audit_master_id = ? AND table_name = 'patient_data'";
 211                                                 $query_res = sqlStatement($query,array($auditmasterid));
 212                                                 while($result = sqlFetchArray($query_res)){
 213                                                   if($result['field_name'] == 'DOB'){
 214                                                                 $dob_res = sqlQuery("SELECT DATE_FORMAT(?,'%Y-%m-%d') as DOB_YMD",array($result['field_value']));
 215                                                                 $res['DOB_YMD'] = $dob_res['DOB_YMD'];
 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              {
 328               $string_query=" and pid !=?";
 329              }
 330              if($string_query)
 331              {
 332              $x=array($data[1][0],$pid);
 333              }
 334              else
 335              {
 336              $x=array($data[1][0]);
 337              }
 338             $query="select count(*) AS count from patient_data where pubpid = ? $string_query";
 339             return array($query,$x);
 340             break;
 341
 342                         //getting DOB and SSN for verifying the duplicate patient existance
 343             case 'P21':
 344             if($pid)
 345              {
 346               $string_query=" and pid !=?";
 347              }
 348              if($string_query)
 349              {
 350              $x=array($data[1][0],$pid);
 351              }
 352              else
 353              {
 354              $x=array($data[1][0]);
 355              }
 356             $query="select  ss,DOB  from patient_data where DOB=? $string_query  ";
 357             return array($query,$x);
 358             break;
 359
 360                         //master data for calendar from Globals
 361             case 'B1':
 362             //patient appointment
 363             if($data[1][0]=='calendar_interval'||$data[1][0]=='schedule_start'||$data[1][0]=='schedule_end')
 364             {
 365             $query="select gl_value from globals where gl_name=?";
 366             return array($query,$data[1]);
 367             }
 368             else
 369             return 0;
 370             break;
 371
 372             case 'B4':
 373              //Check whether an entry exist in the form Encounter to decide whether patient is an existing patient.
 374             $query="select COUNT(*) AS cnt from form_encounter WHERE pid=?";
 375             return array($query,array($pid));
 376             break;
 377
 378             case 'B5':
 379            //Existing appointments for a patient
 380             array_unshift($data[1],$pid);
 381             $query="select pc_eid,pc_eventDate,pc_startTime,pc_endTime,fname,lname,name,pc_apptstatus from openemr_postcalendar_events AS c,
 382             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";
 383             return array($query,$data[1]);
 384             break;
 385
 386             case 'B6':
 387             //Appointments pending for approval
 388             array_push($data[1],$pid);
 389             $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,
 390             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
 391             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')
 392             ORDER BY approval_status, am.id desc,ad.id desc";
 393             return array($query,$data[1]);
 394             break;
 395
 396             case 'B7':
 397             //patient appointment history
 398             array_unshift($data[1],$pid);
 399             $query="select pc_eid,pc_eventDate,pc_startTime,pc_endTime,fname,lname,name,pc_apptstatus from openemr_postcalendar_events AS c,
 400             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";
 401             return array($query,$data[1]);
 402             break;
 403
 404             case 'B8':
 405             //List of Service Facility
 406             $query="select * from facility where service_location != 0 and id in (".add_escape_custom($data[1][0]).") order by name";
 407             return array($query);
 408             break;
 409
 410             case 'B9':
 411             //Providers list
 412             $query="select id, lname, fname from users WHERE authorized = 1 AND username != '' AND username NOT LIKE '%Admin%' AND active = 1
 413             AND ( info IS NULL OR info NOT LIKE '%Inactive%' ) ORDER BY lname, fname";
 414             return array($query);
 415             break;
 416
 417             case 'B10':
 418            //Calendar default visit time for visit category. value for Admin--->others-->calendar
 419             $query="select pc_duration from openemr_postcalendar_categories WHERE pc_catid = ?";
 420             return array($query,$data[1]);
 421             break;
 422
 423             case 'B11';
 424             //patient appointment
 425             $query="select pc_eventDate, pc_endDate, pc_startTime, pc_duration, pc_recurrtype, pc_recurrspec, pc_alldayevent, pc_catid,
 426             pc_prefcatid from openemr_postcalendar_events WHERE pc_aid = ? AND ((pc_endDate >= ? AND pc_eventDate < ?) OR
 427             (pc_endDate = '0000-00-00' AND pc_eventDate >= ? AND pc_eventDate < ?)) AND pc_facility = ?";
 428             return array($query,$data[1]);
 429             break;
 430
 431             case 'B12':
 432             //Appointments pending for approval
 433             $query="select * from audit_master WHERE pid =? AND type='10'";
 434             return array($query,array($pid));
 435             break;
 436
 437             case 'B13':
 438             //patient appointment
 439             $query="select * from facility where service_location != 0 order by name";
 440             return array($query);
 441             break;
 442
 443             //C series for mailbox.
 444             case 'C1':
 445                     $query = "SELECT username, fname, lname FROM users WHERE username != '' AND active = 1 AND ( info IS NULL OR info NOT LIKE
 446                     '%Inactive%' ) ORDER BY lname, fname";
 447                             return array($query);
 448                             break;
 449
 450             case 'C2':
 451                     $query = "SELECT option_id, title FROM list_options WHERE list_id = ? AND activity = 1 ORDER BY seq";
 452                             return array($query,$data[1]);
 453                             break;
 454
 455             //D series for patient.
 456             case 'D1':
 457                     $query = "SELECT forms.encounter, forms.form_id, forms.id, forms.form_name, forms.formdir,forms.date AS fdate,
 458                     form_encounter.date ,form_encounter.reason FROM forms LEFT OUTER JOIN form_encounter ON  forms.pid=form_encounter.pid
 459                     WHERE forms.pid = ? AND forms.deleted=0 AND forms.formdir<>? GROUP BY id ORDER BY forms.encounter,fdate  ASC";
 460                     array_unshift($data[1],$pid);
 461                             return array($query,$data[1]);
 462                             break;
 463
 464             case 'D2':
 465                     $query = "SELECT name FROM registry ORDER BY priority";
 466                             return array($query);
 467                             break;
 468
 469             case 'D3':
 470                     $query = "select * from lists WHERE pid =? ORDER BY type, begdate";
 471                             return array($query,array($pid));
 472                             break;
 473
 474             case 'D4':
 475                     $query = "select encounter from issue_encounter WHERE pid = ? AND list_id = ?";
 476                             array_unshift($data[1],$pid);
 477                             return array($query,$data[1]);
 478                             break;
 479
 480             //G series for form menu inc
 481             case 'G1':
 482                     $query = "SELECT * FROM `documents_legal_categories` where dlc_category_name=? and dlc_category_type=2";
 483                             return array($query,$data[1]);
 484                             break;
 485
 486             case 'G2':
 487                     $query = "SELECT * FROM documents_legal_master AS dlm WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND
 488                     dlm_effective_date<>? AND dlm_upload_type = '0' AND dlm_document_id NOT IN (SELECT DISTINCT (ad2.field_value) FROM
 489                                                                                 audit_details ad JOIN audit_details ad1 ON ad1.table_name = 'patient_access_offsite' AND ad1.field_name = 'portal_pwd'
 490                                                                                 AND ad1.audit_master_id = ad.audit_master_id JOIN audit_details ad2 ON ad2.table_name = 'documents_legal_detail' AND
 491                                                                                 ad2.field_name = 'dld_master_docid' JOIN audit_details ad3 ON ad3.table_name = 'patient_access_offsite' AND
 492                                                                                 ad3.field_name = 'portal_username' AND ad3.audit_master_id = ad2.audit_master_id WHERE ad.audit_master_id = ? AND
 493                                                                                 ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username' AND ad3.field_value = ad.field_value)";
 494                             array_push($data[1],$auditmasterid);
 495                             return array($query,$data[1]);
 496                             break;
 497
 498             case 'G3':
 499                     $query = "SELECT *, ad5.field_value AS dld_filename FROM documents_legal_master AS dlm JOIN audit_details ad ON
 500                                                                                 ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username' JOIN audit_details ad1 ON
 501                                                                                 ad1.table_name = 'patient_access_offsite' AND ad1.field_name = 'portal_pwd' AND ad1.audit_master_id = ad.audit_master_id
 502                                                                                 JOIN audit_details ad2 ON ad2.table_name = 'patient_access_offsite' AND ad2.field_name = 'portal_username' AND
 503                                                                                 ad2.field_value = ad.field_value JOIN audit_details ad3 ON ad3.table_name = 'documents_legal_detail' AND
 504                                                                                 ad3.field_name = 'dld_master_docid' AND ad3.field_value = dlm.dlm_document_id AND ad3.audit_master_id = ad2.audit_master_id
 505                                                                                 JOIN audit_details ad4 ON ad4.table_name = 'documents_legal_detail' AND ad4.field_name = 'dld_signed' AND
 506                                                                                 ad4.audit_master_id = ad3.audit_master_id JOIN audit_details ad5 ON ad5.table_name = 'documents_legal_detail'
 507                                                                                 AND     ad5.field_name = 'dld_filename' AND ad5.audit_master_id = ad3.audit_master_id WHERE dlm_subcategory <> ?
 508                                                                                 AND dlm_effective_date <= NOW() AND dlm_effective_date <> ? AND ad4.field_value = ? AND ad.audit_master_id = ?
 509                                                                                 GROUP BY dlm.dlm_document_id ORDER BY dlm_effective_date DESC";
 510                             array_push($data[1],$auditmasterid);
 511                             return array($query,$data[1]);
 512                             break;
 513
 514             case 'G4':
 515                     $query = "SELECT * FROM documents_legal_master AS dlm JOIN documents_legal_detail as dld ON dlm_document_id=dld_master_docid
 516                     JOIN form_encounter as fe ON encounter=dld_encounter WHERE dlm_subcategory = ? AND dlm_effective_date <= now() AND
 517                     dlm_effective_date<>? AND dld_id IS NOT NULL AND dld_signed=? AND dld_signing_person=? AND dld_pid=?
 518                     ORDER BY dlm_effective_date DESC";
 519                             array_push($data[1],$pid);
 520                             return array($query,$data[1]);
 521                             break;
 522
 523             case 'G5':
 524                     $query = "SELECT * FROM documents_legal_master AS dlm JOIN documents_legal_detail as dld ON dlm_document_id=dld_master_docid
 525                     JOIN form_encounter as fe ON encounter=dld_encounter WHERE dlm_subcategory = ? and dlm_effective_date <= now() AND
 526                     dlm_effective_date<>? AND dld_id IS NOT NULL AND dld_filename != '' AND dld_pid=? GROUP BY dld_encounter,dlm_document_id
 527                     ORDER BY dld_id DESC";
 528                             array_push($data[1],$pid);
 529                             return array($query,$data[1]);
 530                             break;
 531             case 'G6':
 532                     $query = "SELECT * FROM documents_legal_master AS dlm LEFT OUTER JOIN documents_legal_detail as dld ON
 533                     dlm_document_id=dld_master_docid WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND dlm_effective_date<>?
 534                     AND dld_id IS NOT NULL AND (dld_signed = ? OR dlm_upload_type = '1') AND dld_pid=? ORDER BY dlm_effective_date DESC";
 535                             array_push($data[1],$pid);
 536                             return array($query,$data[1]);
 537                             break;
 538
 539             case 'F1':
 540             //Patient details .
 541                                                 $query="SELECT * FROM audit_details WHERE audit_master_id = ? AND table_name = 'patient_data'";
 542                                                 $query_res = sqlStatement($query,array($auditmasterid));
 543                                                 while($result = sqlFetchArray($query_res)){
 544                                                   $res[$result['field_name']] = $result['field_value'];
 545                                                 }
 546             return array($res,'result');
 547             break;
 548
 549             case 'F2':
 550             //PDF forms detail selected
 551             $query="select * from documents_legal_master where dlm_document_id=?";
 552             return array($query,$data[1]);
 553             break;
 554
 555             case 'F3':
 556             //signing
 557             array_unshift($data[1],$pid);
 558             $query="select * from documents_legal_detail where dld_pid = ? and dld_signed='3' and dld_master_docid = ?";
 559             return array($query,$data[1]);
 560             break;
 561
 562             case 'F6':
 563             //signing
 564             $query="select * from documents_legal_master where dlm_document_id=?";
 565             return array($query,$data[1]);
 566             break;
 567
 568             case 'F8':
 569             // Entries to be approved demo  for new patient, existing patient and only documents
 570             $query="select * from audit_master where id=? and approval_status='1' and (type='1' or type='2' or type='3')";
 571             return array($query,array($auditmasterid));
 572             break;
 573
 574             case 'F9':
 575             //signing
 576             $query="select * from documents_legal_master WHERE dlm_document_id=?";
 577             return array($query,$data[1]);
 578             break;
 579
 580             case 'F10':
 581             //Documents ready to be signed and documents unsigned
 582             array_unshift($data[1],$pid);
 583             $query="select * from documents_legal_detail where dld_pid=? and (dld_signed='2' or dld_signed='0') and dld_master_docid=?";
 584             return array($query,$data[1]);
 585             break;
 586
 587
 588             case 'F12':
 589             //Selection from master document for showing to patient
 590             $query="select * from documents_legal_master WHERE dlm_document_name=?";
 591             return array($query,$data[1]);
 592             break;
 593
 594             case 'J1':
 595                                                 //First name of patient
 596             $query = "SELECT field_value as fname FROM audit_details WHERE audit_master_id=? AND table_name='patient_data' AND field_name = 'fname'";
 597             return array($query,array($auditmasterid));
 598             break;
 599             //Checking whether a new patient entry is pending in the audit master
 600             case 'J2':
 601             $query = "SELECT pid FROM audit_master WHERE approval_status=1 and type=1 and pid=?";
 602             return array($query,array($pid));
 603             break;
 604
 605             case 'payment_settings':
 606             $query = "SELECT login_id,transaction_key,md5 FROM payment_gateway_details WHERE service_name=?";
 607             return array($query,$data[1]);
 608             break;
 609
 610             case 'authorizenet_id':
 611             $query = "SELECT authorize_net_id FROM patient_access_offsite WHERE pid=?";
 612             return array($query,array($pid));
 613             break;
 614         }
 615     }
 616 }
 617 ?>