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;
}
