myportal/soap_service/server_existingpatient.php

   1 <?php
   2 // +-----------------------------------------------------------------------------+
   3 // Copyright (C) 2011 Z&H Consultancy Services Private Limited <sam@zhservices.com>
   4 //
   5 //
   6 // This program is free software; you can redistribute it and/or
   7 // modify it under the terms of the GNU General Public License
   8 // as published by the Free Software Foundation; either version 2
   9 // of the License, or (at your option) any later version.
  10 //
  11 //
  12 // This program is distributed in the hope that it will be useful,
  13 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 // GNU General Public License for more details.
  16 //
  17 //
  18 // A copy of the GNU General Public License is included along with this program:
  19 // openemr/interface/login/GnuGPL.html
  20 // For more information write to the Free Software
  21 // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  22 //
  23 // Author:   Eldho Chacko <eldho@zhservices.com>
  24 //           Jacob T Paul <jacob@zhservices.com>
  25 //
  26 // +------------------------------------------------------------------------------+
  27
  28 //SANITIZE ALL ESCAPES
  29 $sanitize_all_escapes=true;
  30 //
  31
  32 //STOP FAKE REGISTER GLOBALS
  33 $fake_register_globals=false;
  34 //
  35
  36 class existingpatient {
  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;
  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             $query="select * from form_encounter where pid=? limit 1 ";
 100             return array($query,array($pid));
 101             break;
 102
 103             case 'A5':
 104             include_once('../../library/formdata.inc.php');
 105             $enc_set_array=array();
 106             $enc_set_array[]=$pid;
 107             if($data[1][1]=='' && $data[1][2]>0)
 108             {
 109             $enc_set= " and encounter=? " ;
 110             $enc_set_array[]=$data[1][2];
 111             }
 112             $provider="";
 113             $provider  =add_escape_custom($data[1][0]);
 114             $query="select fe.id,fe.pid,encounter,date_format(fe.date,'%Y-%m-%d')
 115             as date,concat(pd.lname,' ',pd.fname) as patname,concat(u.lname,', ',u.fname)
 116             as provname,".$provider." from form_encounter fe left outer join users u
 117              on u.id =fe.".$provider." join patient_data pd on pd.pid=fe.pid where
 118              fe.pid=?".  $enc_set ." order by fe.date desc";
 119             return array($query, $enc_set_array);
 120             break;
 121
 122             case 'A6':
 123             $enc_set_array=array();
 124             $enc_set_array[]=$pid;
 125             if($data[1][0]=='' && $data[1][1]>0)
 126             {
 127             $enc_set= " and encounter=? ";
 128             $enc_set_array[]=$data[1][1];
 129             }
 130             $query="select encounter,sum(fee) as copay ".
 131             " from billing where code_type='copay' and pid=? $enc_set group by encounter";
 132             return array($query,$enc_set_array);
 133             break;
 134             //DEtails of CPT, Diagnosis etc of an encounter
 135             case 'A7':
 136             $enc_set_array=array();
 137             $enc_set_array[]=$pid;
 138             if($data[1][0]=='' && $data[1][1]>0)
 139             {
 140             $enc_set= " and encounter=? ";
 141             $enc_set_array[]=$data[1][1];
 142
 143             }
 144             $query="select concat(encounter,code,modifier) as ecm,encounter,code,
 145             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";
 146             return array($query,$enc_set_array);
 147             break;
 148             //Payment details  of an encounter
 149             case 'A8':
 150             $enc_set_array=array();
 151             $enc_set_array[]=$pid;
 152             if($data[1][0]=='' && $data[1][1]>0)
 153             {
 154             $enc_set= " and encounter=? ";
 155             $enc_set_array[]=$data[1][1];
 156             }
 157             $query="select concat(encounter,code,modifier) as pecm,encounter,code,
 158             modifier,pay_amount,adj_amount,payer_type,post_time,account_code,
 159             follow_up_note,memo,date_format(post_time,'%Y-%m-%d') as dtfrom from ar_activity where pid=? $enc_set";
 160             return array($query,$enc_set_array);
 161             break;
 162             case 'A9':
 163                 $query = "SELECT sum(pay_total)  as pay_total FROM ar_session WHERE patient_id=? AND adjustment_code=?";
 164                 return array($query,array($pid,'pre_payment'));
 165             break;
 166             case 'A10':
 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                 $query = "SELECT sum(pay_total)  as pay_total FROM ar_session WHERE patient_id=? AND adjustment_code!=?";
 173                 return array($query,array($pid,'pre_payment'));
 174             break;
 175             case 'A12':
 176                 $query = "SELECT sum(pay_amount)  as pay_amount FROM ar_session,ar_activity WHERE patient_id=? AND adjustment_code!=?
 177                           AND pid=? AND ar_session.session_id=ar_activity.session_id  and pay_amount>0";
 178                 return array($query,array($pid,'pre_payment',$pid));
 179             break;
 180
 181             // Entries pending  for approval for Existing Patient and New Patient.
 182             case 'U4':
 183             $query=  "select * from  audit_master  where pid=? and  approval_status='1' and  (type='1' or type='2')";
 184             return array($query,array($pid));
 185             break;
 186             // Entries pending  for approval for  documents only (no demo change).
 187             case 'U5':
 188             $query = " select * from  audit_master  where pid=? and  approval_status='1' and  type='3' ";
 189             return array($query,$data[1]);
 190             break;
 191
 192             case 'P1':
 193             $query= "select MAX(pid)+1 AS pid  from patient_data ";
 194
 195             return array($query);
 196             break;
 197
 198             //for building patient Demo
 199             case 'P2':
 200             $query="select * from  layout_options " .
 201                 "WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
 202                       "ORDER BY group_name, seq";
 203             return array($query);
 204             break;
 205
 206             //for building patient Demo   Date of Birth
 207             case 'P3':
 208             $query="select  *, DATE_FORMAT(DOB,'%Y-%m-%d') as DOB_YMD from patient_data where pid=? order by date DESC limit 0,1 ";
 209             return array($query,array($pid));
 210             break;
 211
 212            //for building patient Demo   Employer Data
 213             case 'P4':
 214             $query="select  * from employer_data where pid=? order by date DESC limit 0,1 ";
 215             return array($query,array($pid));
 216             break;
 217
 218             //for building patient Demo   Insurance company details for Patient
 219             case 'P5':
 220             $query=" select insd.*, ic.name as provider_name from insurance_data as insd " .
 221                 "left join insurance_companies as ic on ic.id = insd.provider " .
 222                 "where pid = ? and type =? order by date DESC limit 1 ";
 223             array_unshift($data[1],$pid);
 224             return array($query,$data[1]);
 225             break;
 226
 227                         // Entries pending  for approval demo and documents.
 228             case 'P6':
 229             $query=" select * from audit_master as am,audit_details as ad WHERE am.id=ad.audit_master_id and am.pid=? and am.approval_status='1'
 230                             and  (am.type='1' or am.type='2' or am.type='3')  order by ad.id";
 231             return array($query,array($pid));
 232             break;
 233             // Demo building from layout options.
 234
 235             case 'P7':
 236                 $query=" select * from layout_options WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
 237                 "ORDER BY group_name, seq";
 238
 239             return array($query);
 240             break;
 241
 242                         //Global specific application for building demo.
 243             case 'P8':
 244             $query="select * from  globals where gl_name ='specific_application' ";
 245             return array($query);
 246             break;
 247
 248                         //Global omit employers for building demo.
 249             case 'P9':
 250             $query=" select * from globals where gl_name ='omit_employers' ";
 251             return array($query);
 252             break;
 253             //details of COpay and Fees
 254             case 'P10':
 255             array_push($data[1],$pid);
 256             $query= "select date, code_type, code, modifier, code_text, fee , units, justify  from billing WHERE  encounter =? AND pid = ? AND " .
 257                 " activity = 1 AND fee != 0.00 ORDER BY  fee desc,code,modifier";
 258             return array($query,$data[1]);
 259             break;
 260
 261             case 'P11':
 262             $query = "select code_text from codes WHERE  code = ? ";
 263             return array($query,$data[1]);
 264             break;
 265                         //Details of drug sales
 266             case 'P12':
 267             array_push($data[1],$pid);
 268              $query = "select s.drug_id, s.sale_date, s.fee, s.quantity from drug_sales AS s " .
 269                 "WHERE  s.encounter = ? and s.pid = ? AND s.fee != 0 " .
 270                 "ORDER BY s.sale_id";
 271             return array($query,$data[1]);
 272             break;
 273                         //Details of Payments
 274             case 'P14':
 275             array_push($data[1],$pid);
 276                     $query = "Select a.code, a.modifier, a.memo, a.payer_type, a.adj_amount, a.pay_amount, " .
 277                 "a.post_time, a.session_id, a.sequence_no,a.follow_up, a.follow_up_note, " .
 278                 "s.payer_id, s.reference, s.check_date, s.deposit_date " .
 279                 ",i.name from ar_activity AS a " .
 280                 "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 281                 "LEFT OUTER JOIN insurance_companies AS i ON i.id = s.payer_id " .
 282                 "WHERE  a.encounter = ? and a.pid = ? " .
 283                 "ORDER BY s.check_date, a.sequence_no";
 284             return array($query,$data[1]);
 285             break;
 286                         //Address of Billing Facility
 287             case 'P15':
 288             $query = "SELECT f.name,f.street,f.city,f.state,f.postal_code,f.phone from facility f " .
 289             " where  id=?";
 290             return array($query,$data[1]);
 291             break;
 292             //Encounter status primary,secondary Etc
 293             case 'P16':
 294             array_push($data[1],$pid);
 295             $query = "select last_level_closed from form_encounter where encounter= ? and pid =? ";
 296             return array($query,$data[1]);
 297             break;
 298
 299             case 'P17':
 300             $query = "select COUNT( DISTINCT TYPE ) NumberOfInsurance from insurance_data where pid =? and provider>0 ";
 301             return array($query,array($pid));
 302             break;
 303
 304             case 'P19':
 305             $query = "select  date,encounter from form_encounter where pid =? ORDER BY encounter";
 306             return array($query,array($pid));
 307             break;
 308
 309             case 'P20':
 310             if($pid)
 311              {
 312               $string_query=" and pid !=?";
 313              }
 314              if($string_query)
 315              {
 316              $x=array($data[1][0],$pid);
 317              }
 318              else
 319              {
 320              $x=array($data[1][0]);
 321              }
 322             $query="select count(*) AS count from patient_data where pubpid = ? $string_query";
 323             return array($query,$x);
 324             break;
 325
 326                         //getting DOB and SSN for verifying the duplicate patient existance
 327             case 'P21':
 328             if($pid)
 329              {
 330               $string_query=" and pid !=?";
 331              }
 332              if($string_query)
 333              {
 334              $x=array($data[1][0],$pid);
 335              }
 336              else
 337              {
 338              $x=array($data[1][0]);
 339              }
 340             $query="select  ss,DOB  from patient_data where DOB=? $string_query  ";
 341             return array($query,$x);
 342             break;
 343
 344                         //master data for calendar from Globals
 345             case 'B1':
 346             //patient appointment
 347             if($data[1][0]=='calendar_interval'||$data[1][0]=='schedule_start'||$data[1][0]=='schedule_end')
 348             {
 349             $query="select gl_value from globals where gl_name=?";
 350             return array($query,$data[1]);
 351             }
 352             else
 353             return 0;
 354             break;
 355
 356             case 'B4':
 357              //Check whether an entry exist in the form Encounter to decide whether patient is an existing patient.
 358             $query="select COUNT(*) AS cnt from form_encounter WHERE pid=?";
 359             return array($query,array($pid));
 360             break;
 361
 362             case 'B5':
 363            //Existing appointments for a patient
 364             array_unshift($data[1],$pid);
 365             $query="select pc_eid,pc_eventDate,pc_startTime,pc_endTime,fname,lname,name,pc_apptstatus from openemr_postcalendar_events AS c,
 366             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";
 367             return array($query,$data[1]);
 368             break;
 369
 370             case 'B6':
 371             //Appointments pending for approval
 372             array_push($data[1],$pid);
 373             $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,
 374             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
 375             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')
 376             ORDER BY approval_status, am.id desc,ad.id desc";
 377             return array($query,$data[1]);
 378             break;
 379
 380             case 'B7':
 381             //patient appointment history
 382             array_unshift($data[1],$pid);
 383             $query="select pc_eid,pc_eventDate,pc_startTime,pc_endTime,fname,lname,name,pc_apptstatus from openemr_postcalendar_events AS c,
 384             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";
 385             return array($query,$data[1]);
 386             break;
 387
 388             case 'B8':
 389             //List of Service Facility
 390             include_once('../../library/formdata.inc.php');
 391             $query="select * from facility where service_location != 0 and id in (".add_escape_custom($data[1][0]).") order by name";
 392             return array($query);
 393             break;
 394
 395             case 'B9':
 396             //Providers list
 397             $query="select id, lname, fname from users WHERE authorized = 1 AND username != '' AND username NOT LIKE '%Admin%' AND active = 1
 398             AND ( info IS NULL OR info NOT LIKE '%Inactive%' ) ORDER BY lname, fname";
 399             return array($query);
 400             break;
 401
 402             case 'B10':
 403            //Calendar default visit time for visit category. value for Admin--->others-->calendar
 404             $query="select pc_duration from openemr_postcalendar_categories WHERE pc_catid = ?";
 405             return array($query,$data[1]);
 406             break;
 407
 408             case 'B11';
 409             //patient appointment
 410             $query="select pc_eventDate, pc_endDate, pc_startTime, pc_duration, pc_recurrtype, pc_recurrspec, pc_alldayevent, pc_catid,
 411             pc_prefcatid from openemr_postcalendar_events WHERE pc_aid = ? AND ((pc_endDate >= ? AND pc_eventDate < ?) OR
 412             (pc_endDate = '0000-00-00' AND pc_eventDate >= ? AND pc_eventDate < ?)) AND pc_facility = ?";
 413             return array($query,$data[1]);
 414             break;
 415
 416             case 'B12':
 417             //Appointments pending for approval
 418             $query="select * from audit_master WHERE pid =? AND type='10'";
 419             return array($query,array($pid));
 420             break;
 421
 422             case 'B13':
 423             //patient appointment
 424             $query="select * from facility where service_location != 0 order by name";
 425             return array($query);
 426             break;
 427
 428             //C series for mailbox.
 429             case 'C1':
 430                     $query = "SELECT username, fname, lname FROM users WHERE username != '' AND active = 1 AND ( info IS NULL OR info NOT LIKE
 431                     '%Inactive%' ) ORDER BY lname, fname";
 432                             return array($query);
 433                             break;
 434
 435             case 'C2':
 436                     $query = "SELECT option_id, title FROM list_options WHERE list_id = ? ORDER BY seq";
 437                             return array($query,$data[1]);
 438                             break;
 439
 440             //D series for patient.
 441             case 'D1':
 442                     $query = "SELECT forms.encounter, forms.form_id, forms.id, forms.form_name, forms.formdir,forms.date AS fdate,
 443                     form_encounter.date ,form_encounter.reason FROM forms LEFT OUTER JOIN form_encounter ON  forms.pid=form_encounter.pid
 444                     WHERE forms.pid = ? AND forms.deleted=0 AND forms.formdir<>? GROUP BY id ORDER BY forms.encounter,fdate  ASC";
 445                     array_unshift($data[1],$pid);
 446                             return array($query,$data[1]);
 447                             break;
 448
 449             case 'D2':
 450                     $query = "SELECT name FROM registry ORDER BY priority";
 451                             return array($query);
 452                             break;
 453
 454             case 'D3':
 455                     $query = "select * from lists WHERE pid =? ORDER BY type, begdate";
 456                             return array($query,array($pid));
 457                             break;
 458
 459             case 'D4':
 460                     $query = "select encounter from issue_encounter WHERE pid = ? AND list_id = ?";
 461                             array_unshift($data[1],$pid);
 462                             return array($query,$data[1]);
 463                             break;
 464
 465                                                 case 'D5':
 466                     $query = "SELECT po.procedure_order_id, po.date_ordered,po.procedure_type_id AS order_type_id, pt1.name AS procedure_name,
 467                                                                           ptrc.name AS result_category_name, pt2.procedure_type AS result_type, pt2.procedure_type_id AS result_type_id, pt2.name
 468                                                                                 AS result_name, pt2.units AS result_def_units, pt2.range AS result_def_range, pt2.description AS result_description,
 469                                                                                 lo.title AS units_name, pr.procedure_report_id, pr.date_report, pr.date_collected, pr.specimen_num, pr.report_status,
 470                                                                                 pr.review_status, ps.procedure_result_id, ps.abnormal, ps.result, ps.range, ps.result_status, ps.facility, ps.comments
 471                                                                                 FROM procedure_order AS po LEFT JOIN procedure_type AS pt1 ON pt1.procedure_type_id = po.procedure_type_id LEFT JOIN
 472                                                                                 procedure_type AS ptrc ON ptrc.procedure_type_id = pt1.parent AND ptrc.procedure_type LIKE 'grp%' LEFT JOIN procedure_type
 473                                                                                 AS pt2 ON ((ptrc.procedure_type_id IS NULL AND (pt2.parent = po.procedure_type_id OR pt2.procedure_type_id = po.procedure_type_id))
 474                                                                                 OR (pt2.procedure_type_id IS NOT NULL AND pt2.parent = pt1.procedure_type_id)) AND (pt2.procedure_type LIKE 'res%' OR
 475                                                                                 pt2.procedure_type LIKE 'rec%') LEFT JOIN list_options AS lo ON list_id = 'proc_unit' AND option_id = pt2.units LEFT JOIN
 476                                                                                 procedure_report AS pr ON pr.procedure_order_id = po.procedure_order_id LEFT JOIN procedure_result AS ps ON
 477                                                                                 ps.procedure_report_id = pr.procedure_report_id AND ps.procedure_type_id = pt2.procedure_type_id WHERE po.patient_id = ?
 478                                                                                 ORDER BY po.date_ordered, po.procedure_order_id, pr.procedure_report_id, ptrc.seq, ptrc.name, ptrc.procedure_type_id,
 479                                                                           pt2.seq, pt2.name, pt2.procedure_type_id";
 480                             return array($query,array($pid));
 481                             break;
 482
 483             //G series for form menu inc
 484             case 'G1':
 485                     $query = "SELECT * FROM `documents_legal_categories` where dlc_category_name=? and dlc_category_type=2";
 486                             return array($query,$data[1]);
 487                             break;
 488
 489             case 'G2':
 490                     $query = "SELECT * FROM documents_legal_master AS dlm WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND
 491                     dlm_effective_date<>? AND dlm_upload_type = '0' AND dlm_document_id Not IN (SELECT distinct(dld_master_docid) FROM documents_legal_detail WHERE
 492                     dld_id IS NOT NULL AND dld_pid=?)";
 493                             array_push($data[1],$pid);
 494                             return array($query,$data[1]);
 495                             break;
 496
 497             case 'G3':
 498                     $query = "SELECT * FROM documents_legal_master AS dlm LEFT OUTER JOIN documents_legal_detail as dld ON
 499                     dlm_document_id=dld_master_docid WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND dlm_effective_date<>?
 500                     AND dld_id IS NOT NULL AND dld_signed=? AND dld_pid=? ORDER BY dlm_effective_date DESC";
 501                             array_push($data[1],$pid);
 502                             return array($query,$data[1]);
 503                             break;
 504
 505             case 'G4':
 506                     $query = "SELECT * FROM documents_legal_master AS dlm JOIN documents_legal_detail as dld ON dlm_document_id=dld_master_docid
 507                     JOIN form_encounter as fe ON encounter=dld_encounter WHERE dlm_subcategory = ? AND dlm_effective_date <= now() AND
 508                     dlm_effective_date<>? AND dld_id IS NOT NULL AND dld_signed=? AND dld_signing_person=? AND dld_pid=?
 509                     ORDER BY dlm_effective_date DESC";
 510                             array_push($data[1],$pid);
 511                             return array($query,$data[1]);
 512                             break;
 513
 514             case 'G5':
 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_filename != '' AND dld_pid=? GROUP BY dld_encounter,dlm_document_id
 518                     ORDER BY dld_id DESC";
 519                             array_push($data[1],$pid);
 520                             return array($query,$data[1]);
 521                             break;
 522             case 'G6':
 523                     $query = "SELECT * FROM documents_legal_master AS dlm LEFT OUTER JOIN documents_legal_detail as dld ON
 524                     dlm_document_id=dld_master_docid WHERE dlm_subcategory <> ? and dlm_effective_date <= now() AND dlm_effective_date<>?
 525                     AND dld_id IS NOT NULL AND (dld_signed = ? OR dlm_upload_type = '1') AND dld_pid=? ORDER BY dlm_effective_date DESC";
 526                             array_push($data[1],$pid);
 527                             return array($query,$data[1]);
 528                             break;
 529
 530             case 'F1':
 531             //Patient details .
 532             $query="select * from patient_data where pid=?";
 533             return array($query,array($pid));
 534             break;
 535
 536             case 'F2':
 537             //PDF forms detail selected
 538             $query="select * from documents_legal_master where dlm_document_id=?";
 539             return array($query,$data[1]);
 540             break;
 541
 542             case 'F3':
 543             //signing
 544             array_unshift($data[1],$pid);
 545             $query="select * from documents_legal_detail where dld_pid = ? and dld_signed='3' and dld_master_docid = ?";
 546             return array($query,$data[1]);
 547             break;
 548
 549             case 'F6':
 550             //signing
 551             $query="select * from documents_legal_master where dlm_document_id=?";
 552             return array($query,$data[1]);
 553             break;
 554
 555             case 'F8':
 556             // Entries to be approved demo  for new patient, existing patient and only documents
 557             $query="select * from audit_master  where pid=? and  approval_status='1'  and  (type='1' or type='2' or type='3')";
 558             return array($query,array($pid));
 559             break;
 560
 561             case 'F9':
 562             //signing
 563             $query="select * from documents_legal_master WHERE dlm_document_id=?";
 564             return array($query,$data[1]);
 565             break;
 566
 567             case 'F10':
 568             //Documents ready to be signed and documents unsigned
 569             array_unshift($data[1],$pid);
 570             $query="select * from documents_legal_detail where dld_pid=? and (dld_signed='2' or dld_signed='0') and dld_master_docid=?";
 571             return array($query,$data[1]);
 572             break;
 573
 574
 575             case 'F12':
 576             //Selection from master document for showing to patient
 577             $query="select * from documents_legal_master WHERE dlm_document_name=?";
 578             return array($query,$data[1]);
 579             break;
 580
 581             case 'J1':
 582             $query = "SELECT fname FROM patient_data WHERE pid=?";
 583             return array($query,array($pid));
 584             break;
 585             //Checking whether a new patient entry is pending in the audit master
 586             case 'J2':
 587             $query = "SELECT pid FROM audit_master WHERE approval_status=1 and type=1 and pid=?";
 588             return array($query,array($pid));
 589             break;
 590
 591             case 'payment_settings':
 592             $query = "SELECT login_id,transaction_key,md5 FROM payment_gateway_details WHERE service_name=?";
 593             return array($query,$data[1]);
 594             break;
 595
 596             case 'authorizenet_id':
 597             $query = "SELECT authorize_net_id FROM patient_access_offsite WHERE pid=?";
 598             return array($query,array($pid));
 599             break;
 600         }
 601     }
 602 }
 603 ?>