public function getEncounters($data, $getCount = null) { $query_data = array(); $query = "SELECT pd.fname, pd.lname, pd.mname, date(fe.date) as date, fe.pid, fe.encounter,\n u.fname as doc_fname, u.mname as doc_mname, u.lname as doc_lname, (select count(encounter) from form_encounter where pid=fe.pid) as enc_count,\n (SELECT DATE(date) FROM form_encounter WHERE pid=fe.pid ORDER BY date DESC LIMIT 1) as last_visit_date,\n\t\t\t\t\t\t(select count(*) from ccda where pid=fe.pid and transfer=1) as ccda_transfer_count,\n\t\t\t\t\t\t(select count(*) from ccda where pid=fe.pid and transfer=1 and status=1) as ccda_successfull_transfer_count\n FROM form_encounter AS fe\n JOIN patient_data AS pd ON pd.pid=fe.pid\n LEFT JOIN users AS u ON u.id=fe.provider_id "; if ($data['status']) { $query .= " LEFT JOIN combination_form AS cf ON cf.encounter = fe.encounter "; } $query .= " WHERE 1=1 "; if ($data['status'] == "signed") { $query .= " AND cf.encounter IS NOT NULL AND cf.encounter !=''"; } if ($data['status'] == "unsigned") { $query .= " AND (cf.encounter IS NULL OR cf.encounter ='')"; } if ($data['from_date'] && $data['to_date']) { $query .= " AND fe.date BETWEEN ? AND ? "; $query_data[] = $data['from_date']; $query_data[] = $data['to_date']; } if ($data['pid']) { $query .= " AND (fe.pid = ? OR pd.fname like ? OR pd.mname like ? OR pd.lname like ? OR CONCAT_WS(' ',pd.fname,pd.lname) like ?) "; $query_data[] = $data['pid']; $query_data[] = "%" . $data['pid'] . "%"; $query_data[] = "%" . $data['pid'] . "%"; $query_data[] = "%" . $data['pid'] . "%"; $query_data[] = "%" . $data['pid'] . "%"; } if ($data['encounter']) { $query .= " AND fe.encounter = ? "; $query_data[] = $data['encounter']; } $query .= " GROUP BY fe.pid "; $query .= " ORDER BY fe.pid, fe.date "; $appTable = new ApplicationTable(); if ($getCount) { $res = $appTable->zQuery($query, $query_data); $resCount = $res->count(); return $resCount; } $query .= " LIMIT " . \Application\Plugin\CommonPlugin::escapeLimit($data['limit_start']) . "," . \Application\Plugin\CommonPlugin::escapeLimit($data['results']); $resDetails = $appTable->zQuery($query, $query_data); return $resDetails; }
function fetch_result($fromDate, $toDate, $code_selected, $provider_selected, $start, $end, $get_count = null) { $records = array(); $query_string = array(); $query = "SELECT c.code_text,l.pid AS patientid,p.language,l.diagnosis,CONCAT(p.fname, ' ', p.mname, ' ', p.lname) AS patientname,l.date AS issuedate, l.id AS issueid,l.title AS issuetitle \n\t\t\tFROM\n\t\t\t lists l, patient_data p, codes c, form_encounter AS fe \n\t\t\tWHERE c.reportable = 1 "; if ($provider_selected) { $query .= " AND provider_id = ? "; $query_string[] = $provider_selected; } $query .= " AND l.id NOT IN \n\t\t\t(SELECT \n\t\t\t\tlists_id \n\t\t\tFROM\n\t\t\t\tsyndromic_surveillance) \n\t\t\tAND l.date >= ? AND l.date <= ? AND l.pid = p.pid "; $query_string[] = $fromDate; $query_string[] = $toDate; if ($code_selected) { $query .= " AND c.id IN (?) "; $query_string[] = implode(',', $code_selected); } $query .= " AND l.diagnosis LIKE 'ICD9:%' \n\t\t\t\t\tAND ( SUBSTRING(l.diagnosis, 6) = c.code || SUBSTRING(l.diagnosis, 6) = CONCAT_WS('', c.code, ';') ) \n\t\t\t\t\tAND fe.pid = l.pid \n\t\t\t\tUNION DISTINCT \n\t\t\t\tSELECT c.code_text, b.pid AS patientid, p.language, b.code, CONCAT(p.fname, ' ', p.mname, ' ', p.lname) AS patientname, b.date AS issuedate, b.id AS issueid, '' AS issuetitle \n\t\t\t\tFROM\n\t\t\t\t\tbilling b, patient_data p, codes c, form_encounter fe \n\t\t\t\tWHERE c.reportable = 1 \n\t\t\t\t\tAND b.code_type = 'ICD9' AND b.activity = '1' AND b.pid = p.pid AND fe.encounter = b.encounter "; if ($code_selected) { $query .= " AND c.id IN (?) "; $query_string[] = implode(',', $code_selected); } $query .= " AND c.code = b.code \n\t\t\tAND fe.date IN \n\t\t\t(SELECT \n\t\t\t\tMAX(fenc.date) \n\t\t\tFROM\n\t\t\t\tform_encounter AS fenc \n\t\t\tWHERE fenc.pid = fe.pid) "; if ($provider_selected) { $query .= " AND provider_id = ? "; $query_string[] = $provider_selected; } $query .= " AND fe.date >= ? AND fe.date <= ?"; $query_string[] = $fromDate; $query_string[] = $toDate; if ($get_count) { $appTable = new ApplicationTable(); $result = $appTable->zQuery($query, $query_string); foreach ($result as $row) { $records[] = $row; } return count($records); } $query .= " LIMIT " . \Application\Plugin\CommonPlugin::escapeLimit($start) . "," . \Application\Plugin\CommonPlugin::escapeLimit($end); $appTable = new ApplicationTable(); $result = $appTable->zQuery($query, $query_string); foreach ($result as $row) { $records[] = $row; } return $records; }
/** * function immunized patient details * @param type $form_data * @return type */ public function immunizedPatientDetails($form_data, $getCount = null) { $query_data = array(); $query_codes = $form_data['query_codes']; $from_date = $form_data['form_from_date']; $to_date = $form_data['form_to_date']; $form_get_hl7 = $form_data['form_get_hl7']; $fdate = ''; $todate = ''; $query = "SELECT " . "i.patient_id AS patientid, " . "p.language, " . "i.cvx_code , "; if ($form_get_hl7 === 'true') { $query .= "DATE_FORMAT(p.DOB,'%Y%m%d') AS DOB, " . "concat(p.street, '^^', p.city, '^', p.state, '^', p.postal_code) AS address, " . "p.country_code, " . "p.phone_home, " . "p.phone_biz, " . "p.status, " . "p.sex, " . "p.ethnoracial, " . "p.race, " . "p.ethnicity, " . "c.code_text, " . "c.code, " . "c.code_type, " . "DATE_FORMAT(i.vis_date,'%Y%m%d') AS immunizationdate, " . "DATE_FORMAT(i.administered_date,'%Y%m%d') AS administered_date, " . "i.lot_number AS lot_number, " . "i.manufacturer AS manufacturer, " . "concat(p.fname, '^', p.lname) AS patientname, " . "f.facility_code," . "i.administered_by_id,i.note,"; } else { $query .= "concat(p.fname, ' ',p.mname,' ', p.lname) AS patientname, " . "i.vis_date AS immunizationdate, "; } $query .= "i.id AS immunizationid, c.code_text_short AS immunizationtitle " . "FROM (immunizations AS i, patient_data AS p, codes AS c) " . "LEFT JOIN code_types ct ON c.code_type = ct.ct_id " . "LEFT JOIN users AS u ON i.administered_by_id = u.id " . "LEFT JOIN facility AS f ON f.id = u.facility_id " . "WHERE " . "ct.ct_key='CVX' and "; if ($from_date != 0) { $query .= "i.vis_date >= ? "; $query_data[] = $from_date; } if ($from_date != 0 and $to_date != 0) { $query .= " and "; } if ($to_date != 0) { $query .= "i.vis_date <= ? "; $query_data[] = $to_date; } if ($from_date != 0 or $to_date != 0) { $query .= " and "; } $query .= "i.patient_id=p.pid and " . add_escape_custom($query_codes) . "i.cvx_code = c.code "; if ($getCount) { $result = $this->applicationTable->zQuery($query, $query_data); $resCount = $result->count(); return $resCount; } $query .= " LIMIT " . \Application\Plugin\CommonPlugin::escapeLimit($form_data['limit_start']) . "," . \Application\Plugin\CommonPlugin::escapeLimit($form_data['results']); $result = $this->applicationTable->zQuery($query, $query_data); return $result; }
/** * Auto Suggest */ public function listAutoSuggest($post, $limit) { $pages = 0; $limitEnd = \Application\Plugin\CommonPlugin::escapeLimit($limit); if (isset($GLOBALS['set_autosuggest_options'])) { if ($GLOBALS['set_autosuggest_options'] == 1) { $leading = '%'; } else { $leading = $post->leading; } if ($GLOBALS['set_autosuggest_options'] == 2) { $trailing = '%'; } else { $trailing = $post->trailing; } if ($GLOBALS['set_autosuggest_options'] == 3) { $leading = '%'; $trailing = '%'; } } else { $leading = $post->leading; $trailing = $post->trailing; } $queryString = $post->queryString; $page = $post->page; $searchType = $post->searchType; $searchEleNo = $post->searchEleNo; if ($page == '') { $limitStart = 0; } else { $limitStart = \Application\Plugin\CommonPlugin::escapeLimit($page); } $keyword = $leading . $queryString . $trailing; if (strtolower($searchType) == 'patient') { $sql = "SELECT fname, mname, lname, pid, DOB FROM patient_data \n WHERE pid LIKE ? \n OR CONCAT(fname, ' ', lname) LIKE ? \n OR CONCAT(lname, ' ', fname) LIKE ? \n OR DATE_FORMAT(DOB,'%m-%d-%Y') LIKE ? \n OR DATE_FORMAT(DOB,'%d-%m-%Y') LIKE ? \n OR DATE_FORMAT(DOB,'%Y-%m-%d') LIKE ? \n ORDER BY fname "; $result = $this->zQuery($sql, array($keyword, $keyword, $keyword, $keyword, $keyword, $keyword)); $rowCount = $result->count(); $sql .= "LIMIT {$limitStart}, {$limitEnd}"; $result = $this->zQuery($sql, array($keyword, $keyword, $keyword, $keyword, $keyword, $keyword)); } elseif (strtolower($searchType) == 'emrdirect') { $sql = "SELECT fname, mname, lname,email,id FROM users \n WHERE (CONCAT(fname, ' ', lname) LIKE ? \n OR CONCAT(lname, ' ', fname) LIKE ? \n OR email LIKE ?) \n AND abook_type = 'emr_direct' \n AND active = 1\n ORDER BY fname "; $result = $this->zQuery($sql, array($keyword, $keyword, $keyword)); $rowCount = $result->count(); $sql .= "LIMIT {$limitStart}, {$limitEnd}"; $result = $this->zQuery($sql, array($keyword, $keyword, $keyword)); } $arr = array(); if ($result) { foreach ($result as $row) { $arr[] = $row; } $arr['rowCount'] = $rowCount; } return $arr; }
/** * function immunized patient details * @param type $form_data * @return type */ public function immunizedPatientDetails($form_data, $getCount = null) { $query_data = array(); $query_codes = $form_data['query_codes']; $from_date = $form_data['form_from_date']; $to_date = $form_data['form_to_date']; $form_get_hl7 = $form_data['form_get_hl7']; $query_pids = $form_data['query_pids']; $fdate = ''; $todate = ''; $query = "SELECT " . "i.patient_id AS patientid, " . "p.language, " . "i.cvx_code , "; if ($form_get_hl7 === 'true') { $query .= "DATE_FORMAT(p.DOB,'%Y%m%d') AS DOB, " . "p.pubpid, " . "CONCAT(IF(p.street IS NULL,'',p.street), '^^', IF(p.city IS NULL,'',p.city), '^', IF(p.state IS NULL,'',p.state), '^', IF(p.postal_code IS NULL,'',p.postal_code) ,'^', IF(l.option_id IS NULL,'',l.notes)) AS address, " . "p.country_code, " . "p.phone_home, " . "p.phone_biz, " . "p.status, " . "p.sex, " . "p.ethnoracial, " . "p.race, " . "p.ethnicity, " . "p.guardiansname, " . "p.guardianrelationship, " . "p.guardiansex, " . "p.guardianphone, " . "p.guardianworkphone, " . "p.email, " . "p.publicity_code, " . "p.imm_reg_status, " . "p.protect_indicator, " . "DATE_FORMAT(p.prot_indi_effdate,'%Y%m%d') AS protection_effective_date, " . "DATE_FORMAT(p.publ_code_eff_date,'%Y%m%d') AS publicity_code_effective_date, " . "DATE_FORMAT(p.imm_reg_stat_effdate,'%Y%m%d') AS immunization_registry_status_effective_date, " . "CONCAT(IF(p.guardianaddress IS NULL,'',p.guardianaddress), '^', '','^',IF(p.guardiancity IS NULL,'',p.guardiancity), '^', IF(p.guardianstate IS NULL,'',p.guardianstate), '^', IF(p.guardianpostalcode IS NULL,'',p.guardianpostalcode) ,'^', IF(l.option_id IS NULL,'',l.notes),'^','L', '^', '', '^', '', '^', '') AS guardian_address, " . "p.ss, " . "c.code_text, " . "c.code, " . "c.code_type, " . "DATE_FORMAT(i.vis_date,'%Y%m%d') AS immunizationdate, " . "DATE_FORMAT(i.administered_date,'%Y%m%d') AS administered_date, " . "i.lot_number AS lot_number, " . "i.manufacturer AS manufacturer, " . "i.administration_site, " . "CONCAT(IF(p.lname IS NULL,'',p.lname), '^', p.fname , '^', IF(p.mname IS NULL,'',p.mname)) AS patientname, " . "f.id," . "f.name AS fac_name, " . "f.facility_code," . "i.administered_by_id," . "i.note," . "CONCAT (IF(u.npi IS NULL,'',u.npi),'^',u.lname,'^',u.fname,'^',IF(u.mname IS NULL,'',u.mname)) as primary_care_provider_details, " . "l.notes AS country_code, " . "l1.notes AS route_code, " . "i.route, " . "CONCAT(u.lname,'^',u.fname,'^',IF(u.mname IS NULL,'',u.mname),'^','','^',IF(u.title IS NULL,'',u.title),'^','','^','','^','TX','^','L','^','','^','','^','','^','','^','') AS providername, " . "CONCAT(f.id,'^',IF(f.street IS NULL,'',SUBSTRING(f.`street`,1,20)),'^',IF(f.`city` IS NULL,'',f.`city`),'^',IF(f.`state` IS NULL,'',f.`state`),'^',IF(f.`postal_code` IS NULL,'',f.`postal_code`),'^',IF(f.`country_code` IS NULL,'',f.`country_code`)) AS facility_address, " . "u.id AS users_id, " . "i.created_by, " . "i.ordering_provider, " . "CONCAT(u1.lname,'^',u1.fname,'^',IF(u1.mname IS NULL,'',u1.mname)) AS entered_by_name, " . "CONCAT(u2.lname,'^',u2.fname,'^',IF(u2.mname IS NULL,'',u2.mname)) AS ordering_provider_name, " . "i.administered_by_id,i.note,i.information_source,DATE_FORMAT(i.expiration_date,'%Y%m%d') AS expiration_date,i.refusal_reason,i.completion_status,"; } else { $query .= "CONCAT(IF(p.fname IS NULL,'',p.fname),' ',IF(p.mname IS NULL,'',p.mname),' ',IF(p.lname IS NULL,'',p.lname)) AS patientname, " . "i.vis_date AS immunizationdate, "; } $query .= "i.id AS immunizationid, c.code_text_short AS immunizationtitle, c.code_text,i.amount_administered AS administered_amount, i.amount_administered_unit AS administered_unit " . "FROM (immunizations AS i, patient_data AS p, codes AS c) " . "LEFT JOIN code_types ct ON c.code_type = ct.ct_id " . "LEFT JOIN users AS u ON i.administered_by_id = u.id " . "LEFT JOIN facility AS f ON f.id = u.facility_id " . "LEFT JOIN list_options l ON l.option_id = p.country_code AND l.list_id='country' " . "LEFT JOIN list_options l1 ON l1.option_id = i.route AND l1.list_id='drug_route' " . "LEFT JOIN list_options l2 ON l2.option_id = p.guardiancountry AND l2.list_id='country' " . "LEFT JOIN users AS u1 ON i.created_by = u1.id " . "LEFT JOIN users AS u2 ON i.ordering_provider = u2.id " . "WHERE " . "ct.ct_key='CVX' and "; if ($from_date != 0) { $query .= "i.vis_date >= ? "; $query_data[] = $from_date; } if ($from_date != 0 and $to_date != 0) { $query .= " and "; } if ($to_date != 0) { $query .= "i.vis_date <= ? "; $query_data[] = $to_date; } if ($from_date != 0 or $to_date != 0) { $query .= " and "; } $query .= "i.patient_id=p.pid and " . add_escape_custom($query_codes) . $query_pids . "i.cvx_code = c.code ORDER BY i.patient_id, i.id"; if ($getCount) { $result = $this->applicationTable->zQuery($query, $query_data); $resCount = $result->count(); return $resCount; } $query .= " LIMIT " . \Application\Plugin\CommonPlugin::escapeLimit($form_data['limit_start']) . "," . \Application\Plugin\CommonPlugin::escapeLimit($form_data['results']); $result = $this->applicationTable->zQuery($query, $query_data); return $result; }