PHP sqlQuery函数代码示例

function showDocument(&$drow)
{
    global $ISSUE_TYPES, $auth_med;
    $docdate = $drow['docdate'];
    echo "<tr class='text docrow' id='" . htmlspecialchars($drow['id'], ENT_QUOTES) . "' title='" . htmlspecialchars(xl('View document'), ENT_QUOTES) . "'>\n";
    // show date
    echo "<td>" . htmlspecialchars(oeFormatShortDate($docdate), ENT_NOQUOTES) . "</td>\n";
    // show associated issue, if any
    echo "<td>";
    if ($auth_med) {
        $irow = sqlQuery("SELECT type, title, begdate " . "FROM lists WHERE " . "id = ? " . "LIMIT 1", array($drow['list_id']));
        if ($irow) {
            $tcode = $irow['type'];
            if ($ISSUE_TYPES[$tcode]) {
                $tcode = $ISSUE_TYPES[$tcode][2];
            }
            echo htmlspecialchars("{$tcode}: " . $irow['title'], ENT_NOQUOTES);
        }
    } else {
        echo "(" . htmlspecialchars(xl('No access'), ENT_NOQUOTES) . ")";
    }
    echo "</td>\n";
    // show document name and category
    echo "<td colspan='3'>" . htmlspecialchars(xl('Document') . ": " . basename($drow['url']) . ' (' . xl_document_category($drow['name']) . ')', ENT_NOQUOTES) . "</td>\n";
    // skip billing and insurance columns
    if (!$GLOBALS['athletic_team']) {
        echo "<td colspan=5>&nbsp;</td>\n";
    }
    echo "</tr>\n";
}

function procedure_order_report($pid, $encounter, $cols, $id)
{
    $cols = 1;
    // force always 1 column
    $count = 0;
    $data = sqlQuery("SELECT * " . "FROM procedure_order WHERE " . "procedure_order_id = '{$id}' AND activity = '1'");
    if ($data) {
        print "<table cellpadding='0' cellspacing='0'>\n<tr>\n";
        foreach ($data as $key => $value) {
            if ($key == "procedure_order_id" || $key == "pid" || $key == "user" || $key == "groupname" || $key == "authorized" || $key == "activity" || $key == "date" || $value == "" || $value == "0" || $value == "0.00") {
                continue;
            }
            $key = ucwords(str_replace("_", " ", $key));
            if ($key == "Order Priority") {
                print "<td valign='top'><span class='bold'>" . xl($key) . ": </span><span class='text'>" . generate_display_field(array('data_type' => '1', 'list_id' => 'ord_priority'), $value) . " &nbsp;</span></td>\n";
            } else {
                if ($key == "Order Status") {
                    print "<td valign='top'><span class='bold'>" . xl($key) . ": </span><span class='text'>" . generate_display_field(array('data_type' => '1', 'list_id' => 'ord_status'), $value) . " &nbsp;</span></td>\n";
                } else {
                    print "<td valign='top'><span class='bold'>" . xl($key) . ": </span><span class='text'>{$value} &nbsp;</span></td>\n";
                }
            }
            $count++;
            if ($count == $cols) {
                $count = 0;
                print "</tr>\n<tr>\n";
            }
        }
        print "</tr>\n</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));
}

 public function test(CqmPatient $patient, $beginDate, $endDate)
 {
     $age = $patient->calculateAgeOnDate($beginDate);
     if ($age >= 2 && $age <= 18) {
         //Children 2-18 years of age who had an outpatient or emergency department (ED) visit with a diagnosis of pharyngitis during the measurement period and an antibiotic ordered on or three days after the visit
         $pharyngitisArr = array('ICD9:034', 'ICD9:462', 'ICD9:463', 'ICD10:J02.0', 'ICD10:J02.8', 'ICD10:J02.9', 'ICD10:J03.80', 'ICD10:J03.81', 'ICD10:J03.90', 'ICD10:J03.91');
         $query = "SELECT l.title as drug FROM form_encounter fe " . "INNER JOIN openemr_postcalendar_categories opc ON fe.pc_catid = opc.pc_catid " . "INNER JOIN lists l ON l.type = 'medication' AND fe.pid = l.pid " . "WHERE opc.pc_catname = 'Office Visit' ";
         $pharyngitisStr = "(";
         $cnt = 0;
         foreach ($pharyngitisArr as $pharyngitisCode) {
             if ($cnt == 0) {
                 $pharyngitisStr .= " l.diagnosis LIKE '%" . $pharyngitisCode . "%' ";
             } else {
                 $pharyngitisStr .= " OR l.diagnosis LIKE '%" . $pharyngitisCode . "%' ";
             }
             $cnt++;
         }
         $pharyngitisStr .= ")";
         $query .= " AND " . $pharyngitisStr;
         $query .= " AND fe.pid = ? AND (fe.date BETWEEN ? AND ?)";
         $check = sqlQuery($query, array($patient->id, $beginDate, $endDate));
         if ($check['drug'] != "") {
             return true;
         } else {
             return false;
         }
     }
     return false;
 }

function scanned_notes_report($pid, $useless_encounter, $cols, $id)
{
    global $webserver_root, $web_root, $encounter;
    // In the case of a patient report, the passed encounter is vital.
    $thisenc = $useless_encounter ? $useless_encounter : $encounter;
    $count = 0;
    $data = sqlQuery("SELECT * " . "FROM form_scanned_notes WHERE " . "id = '{$id}' AND activity = '1'");
    if ($data) {
        echo "<table cellpadding='0' cellspacing='0'>\n";
        if ($data['notes']) {
            echo " <tr>\n";
            echo "  <td valign='top'><span class='bold'>Comments: </span><span class='text'>";
            echo nl2br($data['notes']) . "</span></td>\n";
            echo " </tr>\n";
        }
        for ($i = -1; true; ++$i) {
            $suffix = $i < 0 ? "" : "-{$i}";
            $imagepath = $GLOBALS['OE_SITE_DIR'] . "/documents/{$pid}/encounters/{$thisenc}_{$id}{$suffix}.jpg";
            $imageurl = "{$web_root}/sites/" . $_SESSION['site_id'] . "/documents/{$pid}/encounters/{$thisenc}_{$id}{$suffix}.jpg";
            if (is_file($imagepath)) {
                echo " <tr>\n";
                echo "  <td valign='top'>\n";
                echo "   <img src='{$imageurl}' />\n";
                echo "  </td>\n";
                echo " </tr>\n";
            } else {
                if ($i >= 0) {
                    break;
                }
            }
        }
        echo "</table>\n";
    }
}

 /**
  *
  * @param RuleCriteria $criteria
  */
 function modify($criteria, $ruleId)
 {
     // get interval
     $result = sqlQuery(self::SQL_RULE_INTERVAL, array($ruleId));
     $criteria->interval = $result['interval'] ? $result['interval'] : 1;
     $criteria->intervalType = $result['value'] ? TimeUnit::from($result['value']) : TimeUnit::from(TimeUnit::Month);
 }

function scanned_notes_report($pid, $useless_encounter, $cols, $id)
{
    global $webserver_root, $web_root, $encounter;
    // In the case of a patient report, the passed encounter is vital.
    $thisenc = $useless_encounter ? $useless_encounter : $encounter;
    $count = 0;
    $data = sqlQuery("SELECT * " . "FROM form_scanned_notes WHERE " . "id = '{$id}' AND activity = '1'");
    if ($data) {
        if ($data['notes']) {
            echo "  <span class='bold'>Comments: </span><span class='text'>";
            echo nl2br($data['notes']) . "</span><br />\n";
        }
        for ($i = -1; true; ++$i) {
            $suffix = $i < 0 ? "" : "-{$i}";
            $imagepath = $GLOBALS['OE_SITE_DIR'] . "/documents/{$pid}/encounters/{$thisenc}_{$id}{$suffix}.jpg";
            $imageurl = "{$web_root}/sites/" . $_SESSION['site_id'] . "/documents/{$pid}/encounters/{$thisenc}_{$id}{$suffix}.jpg";
            if (is_file($imagepath)) {
                echo "   <img src='{$imageurl}'";
                // Flag images with excessive width for possible stylesheet action.
                $asize = getimagesize($imagepath);
                if ($asize[0] > 750) {
                    echo " class='bigimage'";
                }
                echo " />\n";
                echo " <br />\n";
            } else {
                if ($i >= 0) {
                    break;
                }
            }
        }
    }
}

 public function test(CqmPatient $patient, $beginDate, $endDate)
 {
     //Children who are taking antibiotics in the 30 days prior to the diagnosis of pharyngitis
     $pharyngitisArr = array('ICD9:034', 'ICD9:462', 'ICD9:463', 'ICD10:J02.0', 'ICD10:J02.8', 'ICD10:J02.9', 'ICD10:J03.80', 'ICD10:J03.81', 'ICD10:J03.90', 'ICD10:J03.91');
     $query = "SELECT count(*) as cnt FROM form_encounter fe " . "INNER JOIN openemr_postcalendar_categories opc ON fe.pc_catid = opc.pc_catid " . "INNER JOIN lists l ON l.type = 'medication' AND fe.pid = l.pid " . "WHERE opc.pc_catname = 'Office Visit' ";
     $pharyngitisStr = "(";
     $cnt = 0;
     foreach ($pharyngitisArr as $pharyngitisCode) {
         if ($cnt == 0) {
             $pharyngitisStr .= " l.diagnosis LIKE '%" . $pharyngitisCode . "%' ";
         } else {
             $pharyngitisStr .= " OR l.diagnosis LIKE '%" . $pharyngitisCode . "%' ";
         }
         $cnt++;
     }
     $pharyngitisStr .= ")";
     $query .= " AND " . $pharyngitisStr;
     $query .= " AND fe.pid = ? AND (fe.date BETWEEN ? AND ?)";
     $check = sqlQuery($query, array($patient->id, $beginDate, $endDate));
     if ($check['cnt'] > 1) {
         //more than one medication it will exclude
         return true;
     } else {
         return false;
     }
 }

function misc_billing_options_report($pid, $encounter, $cols, $id)
{
    $count = 0;
    $data = formFetch("form_misc_billing_options", $id);
    if ($data) {
        print "<table><tr>";
        foreach ($data as $key => $value) {
            if ($key == "id" || $key == "pid" || $key == "user" || $key == "groupname" || $key == "authorized" || $key == "activity" || $key == "date" || $value == "" || $value == "0" || $value == "0000-00-00 00:00:00" || $value == "0000-00-00") {
                continue;
            }
            if ($value == "1") {
                $value = "yes";
            }
            if ($key === 'box_14_date_qual' || $key === 'box_15_date_qual') {
                $value = text(qual_id_to_description($key, $value));
            }
            if ($key === 'provider_id') {
                $trow = sqlQuery("SELECT id, lname, fname FROM users WHERE " . "id = ? ", array($value));
                $value = $trow['fname'] . ' ' . $trow['lname'];
            }
            $key = ucwords(str_replace("_", " ", $key));
            print "<td><span class=bold>{$key}: </span><span class=text>" . text($value) . "</span></td>";
            $count++;
            if ($count == $cols) {
                $count = 0;
                print "</tr><tr>\n";
            }
        }
    }
    print "</tr></table>";
}

 public function test(AmcPatient $patient, $beginDate, $endDate)
 {
     $procedure_order_id = $patient->object['procedure_order_id'];
     $sql = "SELECT count(r.result) as cnt FROM procedure_result r " . "INNER JOIN procedure_report pr ON pr.procedure_report_id = r.procedure_report_id " . "INNER JOIN procedure_order po ON po.procedure_order_id = pr.procedure_order_id " . "WHERE r.result !=  '' " . "AND po.procedure_order_id = ?";
     $check = sqlQuery($sql, array($procedure_order_id));
     return $check['cnt'];
 }

function section_jointeam_doJoin($id, $invite)
{
    // Try to add player to team
    $obj = mysql_fetch_object(mysql_query("select l_team.id, l_team.name, l_team.leader, count(callsign) nump\n      from l_team left join l_player  on l_team.id = l_player.team\n      where l_team.id = " . $id . "\n      group by l_team.id, l_team.name, l_team.leader"));
    if ($obj->nump == $TEAMSIZE) {
        // Team full
        echo "<center>Sorry, this team is full. Probably someone was joining it at the same time...</center>";
    } else {
        // Okay, let's do the update
        mysql_query("update l_player set team=" . $id . " where id=" . $_SESSION['playerid']);
        // Close team if it's full
        if ($obj->nump == $TEAMSIZE - 1) {
            mysql_query("update l_team set status='closed' where id=" . $id);
        }
        // Close team if it has 3 players and is adminclosed
        if ($obj->nump == 2) {
            mysql_query("update l_team set status='closed' where adminclosed='yes' and id=" . $id);
        }
        echo '<center>You are now a member of the ' . teamLink($obj->name, $obj->id, false) . ' team.<BR> 
        A message has been sent to the team leader.</center>';
        session_refresh();
        // Send a message to the team leader
        $player = playerLink($_SESSION['playerid'], $_SESSION['callsign']);
        if ($invite) {
            $msg = "{$player} has accepted your invitation, and has joined your team!<BR>";
            sqlQuery("DELETE FROM bzl_invites WHERE teamid={$obj->id}  AND \n            playerid={$_SESSION['playerid']}");
        } else {
            $msg = "A new player just joined your team: {$player}";
        }
        sendBzMail(0, $obj->leader, $_SESSION['callsign'] . ' joined your team!', $msg);
    }
}

function ippf_srh_report($pid, $encounter, $cols, $id)
{
    require_once $GLOBALS["srcdir"] . "/options.inc.php";
    echo "<table>\n";
    display_layout_rows('SRH', sqlQuery("SELECT * FROM form_ippf_srh WHERE id = '{$id}'"));
    echo "</table>\n";
}

function timetable($hour, $row)
{
    global $rs;
    // if $rs is null then query database (this should be executed only once - first time)
    if ($rs === null) {
        // first column of the query is used as key in returned array
        $rs = sqlQuery("select concat(t.tbl_row,'_',t.tbl_col) as pos, t.tbl_id, t.sub_id, s.sub_name\n\t\t\t\t\t\tfrom redips_timetable t, redips_subject s\n\t\t\t\t\t\twhere t.sub_id = s.sub_id", 0);
    }
    print '<tr>';
    print '<td class="mark dark">' . $hour . '</td>';
    // column loop starts from 1 because column 0 is for hours
    for ($col = 1; $col <= 5; $col++) {
        // create table cell
        print '<td>';
        // prepare position key in the same way as the array key looks
        $pos = $row . '_' . $col;
        // if content for the current table cell exists
        if (array_key_exists($pos, $rs)) {
            // prepare elements for defined position (it can be more than one element per table cell)
            $elements = $rs[$pos];
            // id of DIV element will start with sub_id and followed with 'b' (because cloned elements on the page have 'c') and with tbl_id
            // this way content from the database will not be in collision with new content dragged from the left table and each id stays unique
            $id = $elements[2] . 'b' . $elements[1];
            $name = $elements[3];
            $class = substr($id, 0, 2);
            // class name is only first 2 letters from ID
            print "<div id=\"{$id}\" class=\"redips-drag {$class}\">{$name}</div>";
        }
        // close table cell
        print '</td>';
    }
    print "</tr>\n";
}

function fetchData()
{
    global $greyVid, $bradyVids, $lastUpdate;
    $greyVid = sqlQuery("SELECT * FROM Video WHERE creator='C.G.P. Grey' ORDER BY uploaddate DESC LIMIT 1")[0];
    $bradyVids = sqlQuery("SELECT * FROM Video WHERE creator='Brady Haran' AND uploaddate > \$1 ORDER BY uploaddate DESC", array($greyVid["uploaddate"]));
    $lastUpdate = sqlQuery("SELECT * FROM UpdateLog ORDER BY updatedatetime DESC LIMIT 1")[0]['updatedatetime'];
}

 /**
  * Retrieve and existing private key based on the public key value and delete it.
  * This function also garbage collects any "stale/unused" pairs that are older than 5 minutes (300 seconds)
  * 
  * @param type $pub
  */
 public function load_from_db($pub)
 {
     $res = sqlQuery("SELECT private FROM rsa_pairs where public=?", array($pub));
     $this->privKey = $res['private'];
     // Delete this pair, and garbage collect any pairs older than 5 minutes.
     sqlQuery("DELETE FROM rsa_pairs where public=? OR timestampdiff(second,created,now()) > ?", array($pub, 300));
 }