PHP sqlStatement函数代码示例

function newpatient_report($pid, $encounter, $cols, $id)
{
    $res = sqlStatement("select * from form_encounter where pid='{$pid}' and id='{$id}'");
    print "<table><tr><td>\n";
    while ($result = sqlFetchArray($res)) {
        print "<span class=bold>" . xl('Reason') . ": </span><span class=text>" . $result["reason"] . "<br>\n";
        print "<span class=bold>" . xl('Facility') . ": </span><span class=text>" . $result["facility"] . "<br>\n";
    }
    print "</td></tr></table>\n";
}

/**
 * Return listing of immunizations for a patient.
 *
 * @param   string    $pid        person id
 * @param   string    $sortby     sorting field ('vacc' sorts by name, otherwise will sort by date)
 * @param   boolean   $showError  indicator whether to retrieve the records that were added erroneously
 * @return  recordset             listing of immunizations for a patient
 */
function getImmunizationList($pid,$sortby,$showError) {
        $sql = "select i1.id ,i1.immunization_id, i1.cvx_code, i1.administered_date, c.code_text_short, c.code".
                ",i1.manufacturer ,i1.lot_number, i1.completion_status ".
                ",ifnull(concat(u.lname,', ',u.fname),'Other') as administered_by ".
                ",i1.education_date ,i1.note ". ",i1.expiration_date " .
                ",i1.amount_administered, i1.amount_administered_unit, i1.route, i1.administration_site, i1.added_erroneously".
                " from immunizations i1 ".
                " left join users u on i1.administered_by_id = u.id ".
                " left join code_types ct on ct.ct_key = 'CVX' ".
                " left join codes c on c.code_type = ct.ct_id AND i1.cvx_code = c.code ".
                " where i1.patient_id = ? ";
        if (!$showError) {
                $sql .= "and i1.added_erroneously = 0 ";
        }

        $sql .= " order by ";
        
        if ($sortby == "vacc") {
                $sql .= " c.code_text_short, i1.immunization_id, i1.administered_date DESC";
        }
        else {
                $sql .= " administered_date desc";
        }

        $results = sqlStatement($sql,array($pid));
        return $results;
}

function issue_football_injury_save($issue)
{
    global $arr_activity, $arr_sanction, $arr_weather;
    $query = "REPLACE INTO lists_football_injury ( " . "id, fiinjmin, fiinjtime, fimatchtype, ";
    foreach ($arr_activity as $key => $value) {
        $query .= "fimech_{$key}, ";
    }
    foreach ($arr_sanction as $key => $value) {
        $query .= "fimech_{$key}, ";
    }
    foreach ($arr_weather as $key => $value) {
        $query .= "fiweather_{$key}, ";
    }
    $query .= "fimech_othercon, fimech_othernon, fiweather_temperature, " . "fisurface, fiposition, fifootwear, firemoved, ficondition " . ") VALUES ( " . $issue . ", " . invalue('form_injmin') . ", " . rbvalue('form_injtime') . ", " . rbvalue('form_matchtype') . ", ";
    foreach ($arr_activity as $key => $value) {
        $query .= cbvalue("form_mech_{$key}") . ", ";
    }
    foreach ($arr_sanction as $key => $value) {
        $query .= cbvalue("form_mech_{$key}") . ", ";
    }
    foreach ($arr_weather as $key => $value) {
        $query .= cbvalue("form_weather_{$key}") . ", ";
    }
    $query .= txvalue('form_mech_othercon') . ", " . txvalue('form_mech_othernon') . ", " . txvalue('form_weather_temperature') . ", " . rbvalue('form_surface') . ", " . rbvalue('form_position') . ", " . rbvalue('form_footwear') . ", " . rbvalue('form_removed') . ", " . rbvalue('form_condition') . " " . ")";
    sqlStatement($query);
}

function checkCreateCDB()
{
    $globalsres = sqlStatement("SELECT gl_name, gl_index, gl_value FROM globals WHERE gl_name IN \n  ('couchdb_host','couchdb_user','couchdb_pass','couchdb_port','couchdb_dbase','document_storage_method')");
    $options = array();
    while ($globalsrow = sqlFetchArray($globalsres)) {
        $GLOBALS[$globalsrow['gl_name']] = $globalsrow['gl_value'];
    }
    $directory_created = false;
    if ($GLOBALS['document_storage_method'] != 0) {
        // /documents/temp/ folder is required for CouchDB
        if (!is_dir($GLOBALS['OE_SITE_DIR'] . '/documents/temp/')) {
            $directory_created = mkdir($GLOBALS['OE_SITE_DIR'] . '/documents/temp/', 0777, true);
            if (!$directory_created) {
                echo htmlspecialchars(xl("Failed to create temporary folder. CouchDB will not work."), ENT_NOQUOTES);
            }
        }
        $couch = new CouchDB();
        if (!$couch->check_connection()) {
            echo "<script type='text/javascript'>alert('" . addslashes(xl("CouchDB Connection Failed.")) . "');</script>";
            return;
        }
        if ($GLOBALS['couchdb_host'] || $GLOBALS['couchdb_port'] || $GLOBALS['couchdb_dbase']) {
            $couch->createDB($GLOBALS['couchdb_dbase']);
            $couch->createView($GLOBALS['couchdb_dbase']);
        }
    }
    return true;
}

/**
 * Offsite Portal connection function library.
 *
 * Copyright (C) 2013 Z&H Consultancy Services Private Limited <sam@zhservices.com>
 *
 * LICENSE: This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
 *
 * @package OpenEMR
 * @author  Eldho Chacko <eldho@zhservices.com>
 * @author  Vinish K <vinish@zhservices.com>
 * @link    http://www.open-emr.org
 */

function portal_connection(){
    global $credentials;
    $password 	= $GLOBALS['portal_offsite_password'];
    $randkey	= '';
    $timminus = date("Y-m-d H:m",(strtotime(date("Y-m-d H:m"))-7200)).":00";
    sqlStatement("DELETE FROM audit_details WHERE audit_master_id IN(SELECT id FROM audit_master WHERE type=5 AND created_time<=?)",array($timminus));
    sqlStatement("DELETE FROM audit_master WHERE type=5 AND created_time<=?",array($timminus));
    do{
        $randkey 	= substr(md5(rand().rand()), 0, 8);
        $res 	= sqlStatement("SELECT * FROM audit_details WHERE field_value = ?",array($randkey));
        $cnt 	= sqlNumRows($res);
    }
    while($cnt>0);
    $password 	= sha1($password.gmdate("Y-m-d H").$randkey);
    $grpID 	= sqlInsert("INSERT INTO audit_master SET type=5");
    sqlStatement("INSERT INTO audit_details SET field_value=? , audit_master_id=?",array($randkey,$grpID));
    $credentials 	= array($GLOBALS['portal_offsite_username'],$password,$randkey);
    //CALLING WEBSERVICE ON THE PATIENT-PORTAL 
    $client 	= new SoapClient(null, array(
            'location' => $GLOBALS['portal_offsite_address_patient_link']."/webservice/webserver.php",
            'uri'      => "urn://portal/req"
        )
    );
    return $client;
}

function catch_logs()
{
    $sql = sqlStatement("select * from log where id not in(select log_id from log_validator) and checksum is NOT null and checksum != ''");
    while ($row = sqlFetchArray($sql)) {
        sqlInsert("INSERT into log_validator (log_id,log_checksum) VALUES(?,?)", array($row['id'], $row['checksum']));
    }
}

 private function getListOptionById($id)
 {
     $query = "SELECT * " . "FROM `list_options` " . "WHERE list_id = ? " . "AND option_id = ?";
     $results = sqlStatement($query, array($this->getListId(), $id));
     $arr = sqlFetchArray($results);
     return $arr;
 }

 public static function generate_validate_constraints($form_id)
 {
     $fres = sqlStatement("SELECT layout_options.*,list_options.notes as validation_json \n              FROM layout_options  \n              LEFT JOIN list_options ON layout_options.validation=list_options.option_id AND list_options.list_id = 'LBF_Validations'\n              WHERE layout_options.form_id = ? AND layout_options.uor > 0 AND layout_options.field_id != '' \n              ORDER BY layout_options.group_name, layout_options.seq ", array($form_id));
     $constraints = array();
     $validation_arr = array();
     $required = array();
     while ($frow = sqlFetchArray($fres)) {
         $id = 'form_' . $frow['field_id'];
         $validation_arr = array();
         $required = array();
         //Keep "required" option from the LBF form
         if ($frow['uor'] == 2) {
             $required = array(self::VJS_KEY_REQUIRED => true);
         }
         if ($frow['validation_json']) {
             if (json_decode($frow['validation_json'])) {
                 $validation_arr = json_decode($frow['validation_json'], true);
             } else {
                 trigger_error($frow['validation_json'] . " is not a valid json ", E_USER_WARNING);
             }
         }
         if (!empty($required) || !empty($validation_arr)) {
             $constraints[$id] = array_merge($required, $validation_arr);
         }
     }
     return json_encode($constraints);
 }

function lbf_report($pid, $encounter, $cols, $id, $formname)
{
    require_once $GLOBALS["srcdir"] . "/options.inc.php";
    $arr = array();
    $shrow = getHistoryData($pid);
    $fres = sqlStatement("SELECT * FROM layout_options " . "WHERE form_id = ? AND uor > 0 " . "ORDER BY group_name, seq", array($formname));
    while ($frow = sqlFetchArray($fres)) {
        $field_id = $frow['field_id'];
        $currvalue = '';
        if ($frow['edit_options'] == 'H') {
            if (isset($shrow[$field_id])) {
                $currvalue = $shrow[$field_id];
            }
        } else {
            $currvalue = lbf_current_value($frow, $id, $encounter);
            if ($currvalue === FALSE) {
                continue;
            }
            // should not happen
        }
        // For brevity, skip fields without a value.
        if ($currvalue === '') {
            continue;
        }
        // $arr[$field_id] = $currvalue;
        // A previous change did this instead of the above, not sure if desirable? -- Rod
        $arr[$field_id] = wordwrap($currvalue, 30, "\n", true);
    }
    echo "<table>\n";
    display_layout_rows($formname, $arr);
    echo "</table>\n";
}

function load_menu($menu_set)
{
    $menuTables = " SHOW TABLES LIKE ?";
    $res = sqlQuery($menuTables, array("menu_trees"));
    if ($res === false) {
        return array();
    }
    $menuQuery = " SELECT * FROM menu_trees, menu_entries WHERE menu_trees.entry_id=menu_entries.id AND menu_set=? ORDER BY parent, seq";
    $res = sqlStatement($menuQuery, array($menu_set));
    $retval = array();
    $entries = array();
    $parent_not_found = array();
    while ($row = sqlFetchArray($res)) {
        $entries[$row['entry_id']] = menu_entry_to_object($row);
        if (empty($row['parent'])) {
            array_push($retval, $entries[$row['entry_id']]);
        } else {
            if (isset($entries[$row['parent']])) {
                $parent = $entries[$row['parent']];
                array_push($parent->children, $entries[$row['entry_id']]);
            } else {
                array_push($parent_not_found, $entries[$row['entry_id']]);
            }
        }
    }
    foreach ($parent_not_found as $row) {
        if (isset($entries[$row->parent])) {
            $parent = $entries[$row->parent];
            array_push($parent->children, $row);
        } else {
            array_push($parent_not_found2, $row);
        }
    }
    return json_decode(json_encode($retval));
}

function showExamLine($line_id, $description, &$linedbrow, $sysnamedisp)
{
    $dres = sqlStatement("SELECT * FROM form_physical_exam_diagnoses " . "WHERE line_id = '{$line_id}' ORDER BY ordering, diagnosis");
    echo " <tr>\n";
    echo "  <td align='center'><input type='checkbox' name='form_obs[{$line_id}][wnl]' " . "value='1'" . ($linedbrow['wnl'] ? " checked" : "") . " /></td>\n";
    echo "  <td align='center'><input type='checkbox' name='form_obs[{$line_id}][abn]' " . "value='1'" . ($linedbrow['abn'] ? " checked" : "") . " /></td>\n";
    echo "  <td nowrap>{$sysnamedisp}</td>\n";
    echo "  <td nowrap>{$description}</td>\n";
    echo "  <td><select name='form_obs[{$line_id}][diagnosis]' onchange='seldiag(this, \"{$line_id}\")' style='width:100%'>\n";
    echo "   <option value=''></option>\n";
    $diagnosis = $linedbrow['diagnosis'];
    while ($drow = sqlFetchArray($dres)) {
        $sel = '';
        $diag = $drow['diagnosis'];
        if ($diagnosis && $diag == $diagnosis) {
            $sel = 'selected';
            $diagnosis = '';
        }
        echo "   <option value='{$diag}' {$sel}>{$diag}</option>\n";
    }
    // If the diagnosis was not in the standard list then it must have been
    // there before and then removed.  In that case show it in parentheses.
    if ($diagnosis) {
        echo "   <option value='{$diagnosis}' selected>({$diagnosis})</option>\n";
    }
    echo "   <option value='*'>-- Edit --</option>\n";
    echo "   </select></td>\n";
    echo "  <td><input type='text' name='form_obs[{$line_id}][comments]' " . "size='20' maxlength='250' style='width:100%' " . "value='" . htmlentities($linedbrow['comments']) . "' /></td>\n";
    echo " </tr>\n";
}

 public function doPatientCheck(RsPatient $patient, $beginDate = null, $endDate = null, $options = null)
 {
     $return = false;
     $listOptions = Codes::lookup($this->getOptionId(), 'CVX');
     if (count($listOptions) > 0) {
         $sqlQueryBind = array();
         $query = "SELECT * " . "FROM immunizations " . "WHERE patient_id = ? AND added_erroneously = '0' " . "AND administered_date >= ? " . "AND administered_date <= ? ";
         $query .= "AND ( ";
         $count = 0;
         array_push($sqlQueryBind, $patient->id, $beginDate, $endDate);
         foreach ($listOptions as $option_id) {
             $query .= "cvx_code = ? ";
             $count++;
             if ($count < count($listOptions)) {
                 $query .= "OR ";
             }
             array_push($sqlQueryBind, $option_id);
         }
         $query .= " ) ";
         $result = sqlStatement($query, $sqlQueryBind);
         $rows = array();
         for ($iter = 0; $row = sqlFetchArray($result); $iter++) {
             $rows[$iter] = $row;
         }
         if (isset($options[self::OPTION_COUNT]) && count($rows) >= $options[self::OPTION_COUNT]) {
             $return = true;
         } else {
             if (!isset($options[self::OPTION_COUNT]) && count($rows) > 0) {
                 $return = true;
             }
         }
     }
     return $return;
 }

function graphsGetValues($name) {
  global $is_lbf, $pid, $table;
  if ($is_lbf) {
    // Like below, but for LBF data.
    $values = sqlStatement("SELECT " .
      "ld.field_value AS " . add_escape_custom($name) . ", " .
      "UNIX_TIMESTAMP(f.date) as unix_date " .
      "FROM forms AS f, lbf_data AS ld WHERE " .
      "f.pid = ? AND " .
      "f.formdir = ? AND " .
      "f.deleted = 0 AND " .
      "ld.form_id = f.form_id AND " .
      "ld.field_id = ? AND " .
      "ld.field_value != '0' " .
      "ORDER BY f.date",
      array($pid, $table, $name));
  }
  else {
    // Collect the pertinent info and ranges
    //  (Note am skipping values of zero, this could be made to be
    //   optional in the future when using lab values)
    $values = SqlStatement("SELECT " .
      add_escape_custom($name) . ", " .
      "UNIX_TIMESTAMP(date) as unix_date " .
      "FROM " . add_escape_custom($table) . " " .
      "WHERE " . add_escape_custom($name) . " != 0 " .
      "AND pid = ? ORDER BY date", array($pid));
  }
  return $values;
}

 public function test(CqmPatient $patient, $beginDate, $endDate)
 {
     // Flow of control loop
     $return = false;
     do {
         // See if BMI has been recorded between >=22kg/m2 and <30kg/m2 6 months before, or simultanious to the encounter
         $query = "SELECT form_vitals.BMI " . "FROM `form_vitals` " . "LEFT JOIN `form_encounter` " . "ON ( form_vitals.pid = form_encounter.pid ) " . "LEFT JOIN `enc_category_map` " . "ON (enc_category_map.main_cat_id = form_encounter.pc_catid) " . "WHERE form_vitals.BMI IS NOT NULL " . "AND form_vitals.BMI IS NOT NULL " . "AND form_vitals.pid = ? AND form_vitals.BMI >= 22 AND form_vitals.BMI < 30 " . "AND DATE( form_vitals.date ) >= DATE_ADD( form_encounter.date, INTERVAL -6 MONTH ) " . "AND DATE( form_vitals.date ) <= DATE( form_encounter.date ) " . "AND ( enc_category_map.rule_enc_id = 'enc_outpatient' )";
         $res = sqlStatement($query, array($patient->id));
         $number = sqlNumRows($res);
         if ($number >= 1) {
             $return = true;
             break;
         }
         // See if BMI has been recorded >=30kg/m2 6 months before, or simultanious to the encounter
         // AND ÒCare goal: follow-up plan BMI managementÓ OR ÒCommunication provider to provider: dietary consultation orderÓ
         $query = "SELECT form_vitals.BMI " . "FROM `form_vitals` " . "LEFT JOIN `form_encounter` " . "ON ( form_vitals.pid = form_encounter.pid ) " . "LEFT JOIN `enc_category_map` " . "ON (enc_category_map.main_cat_id = form_encounter.pc_catid) " . "WHERE form_vitals.BMI IS NOT NULL " . "AND form_vitals.BMI IS NOT NULL " . "AND form_vitals.pid = ? AND form_vitals.BMI >= 30 " . "AND ( DATE( form_vitals.date ) >= DATE_ADD( form_encounter.date, INTERVAL -6 MONTH ) ) " . "AND ( DATE( form_vitals.date ) <= DATE( form_encounter.date ) ) " . "AND ( enc_category_map.rule_enc_id = 'enc_outpatient' )";
         $res = sqlStatement($query, array($patient->id));
         $number = sqlNumRows($res);
         if ($number >= 1 && (Helper::check(ClinicalType::CARE_GOAL, CareGoal::FOLLOW_UP_PLAN_BMI_MGMT, $patient) || Helper::check(ClinicalType::COMMUNICATION, Communication::DIET_CNSLT, $patient))) {
             $return = true;
             break;
         }
         // See if BMI has been recorded <22kg/m2 6 months before, or simultanious to the encounter
         // AND ÒCare goal: follow-up plan BMI managementÓ OR ÒCommunication provider to provider: dietary consultation orderÓ
         $query = "SELECT form_vitals.BMI " . "FROM `form_vitals` " . "LEFT JOIN `form_encounter` " . "ON ( form_vitals.pid = form_encounter.pid ) " . "LEFT JOIN `enc_category_map` " . "ON (enc_category_map.main_cat_id = form_encounter.pc_catid) " . "WHERE form_vitals.BMI IS NOT NULL " . "AND form_vitals.BMI IS NOT NULL " . "AND form_vitals.pid = ? AND form_vitals.BMI < 22 " . "AND ( DATE( form_vitals.date ) >= DATE_ADD( form_encounter.date, INTERVAL -6 MONTH ) ) " . "AND ( DATE( form_vitals.date ) <= DATE( form_encounter.date ) ) " . "AND ( enc_category_map.rule_enc_id = 'enc_outpatient' )";
         $res = sqlStatement($query, array($patient->id));
         $number = sqlNumRows($res);
         if ($number >= 1 && (Helper::check(ClinicalType::CARE_GOAL, CareGoal::FOLLOW_UP_PLAN_BMI_MGMT, $patient) || Helper::check(ClinicalType::COMMUNICATION, Communication::DIET_CNSLT, $patient))) {
             $return = true;
             break;
         }
     } while (false);
     return $return;
 }

function fetchEvents($from_date, $to_date, $where_param = null, $orderby_param = null)
{
    $where = "( (e.pc_endDate >= '{$from_date}' AND e.pc_eventDate <= '{$to_date}' AND e.pc_recurrtype = '1') OR " . "(e.pc_eventDate >= '{$from_date}' AND e.pc_eventDate <= '{$to_date}') )";
    if ($where_param) {
        $where .= $where_param;
    }
    $order_by = "e.pc_eventDate, e.pc_startTime";
    if ($orderby_param) {
        $order_by = $orderby_param;
    }
    $query = "SELECT " . "e.pc_eventDate, e.pc_endDate, e.pc_startTime, e.pc_endTime, e.pc_duration, e.pc_recurrtype, e.pc_recurrspec, e.pc_recurrfreq, e.pc_catid, e.pc_eid, " . "e.pc_title, e.pc_hometext, " . "p.fname, p.mname, p.lname, p.pid, p.pubpid, " . "u.fname AS ufname, u.mname AS umname, u.lname AS ulname, u.id AS uprovider_id, " . "c.pc_catname, c.pc_catid " . "FROM openemr_postcalendar_events AS e " . "LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid " . "LEFT OUTER JOIN users AS u ON u.id = e.pc_aid " . "LEFT OUTER JOIN openemr_postcalendar_categories AS c ON c.pc_catid = e.pc_catid " . "WHERE {$where} " . "ORDER BY {$order_by}";
    $res = sqlStatement($query);
    $events = array();
    if ($res) {
        while ($row = sqlFetchArray($res)) {
            // if it's a repeating appointment, fetch all occurances in date range
            if ($row['pc_recurrtype']) {
                $reccuringEvents = getRecurringEvents($row, $from_date, $to_date);
                $events = array_merge($events, $reccuringEvents);
            } else {
                $events[] = $row;
            }
        }
    }
    return $events;
}