PHP DAO::setSQL方法代码示例

/**
 * Dumps the Founder data into a table
 *
 * @param int $CompanyID
 */
function dumpFounderData($CompanyID)
{
    // include the data access class
    include_once "DAO.php";
    try {
        // create a new data access object
        $db = new DAO();
        $sql = "EXEC dbo.GetFounderByCompanyID @CompanyID = " . $CompanyID;
        // set the select statement
        $db->setSQL($sql);
        // execute the SQL
        if ($db->execute()) {
            // did we get some rows
            if (sqlsrv_has_rows($db->getResultSet())) {
                // output the table and the first row (column headers)
                echo '<br>';
                echo '<table class="sorted table-autosort:0 table-stripeclass:alternate">';
                echo "<thead><tr>";
                echo "<th class='table-sortable:default' width='100'>Name</th>";
                echo "</tr></thead><tbody>";
                // output the table rows
                while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                    echo '<tr><td class="left" width="70"><a href="../Participant/History.php?wfID=' . $row['ID'] . '" target="_blank">' . $row['FounderName'] . '</a></td></tr>';
                }
                // finish the table
                echo "</tbody></table>";
            } else {
                echo "<div class='err'>No data found.</div>";
            }
        }
    } catch (Exception $e) {
        echo $e->getMessage(), "\n";
    }
    echo "</br>";
}

/**
 * Execute Query to obtain one or more objects from the NECLIMS db; returns string on error
 *
 * @param $sql
 * @param optional class specification
 */
function query($sql, $object = NULL)
{
    // include object class if specifed
    if ($object != NULL) {
        require_once $object . ".cls.php";
    }
    // create a data access object
    $dao = new DAO();
    // pass the sql statement to the data access object
    $dao->setSQL($sql);
    // declare an array for storing the row results
    $retVal = array();
    try {
        // run the sql statement
        if ($dao->execute() && sqlsrv_has_rows($dao->getResultSet())) {
            // object specified.
            if ($object != NULL) {
                // while there were more results/rows, save the object in the array
                while ($row = sqlsrv_fetch_object($dao->getResultSet(), $object . "")) {
                    $retVal[] = $row;
                }
            } else {
                // while there were more results/rows, save the object in the array
                while ($row = sqlsrv_fetch_array($dao->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                    $retVal[] = $row;
                }
            }
        }
    } catch (Exception $e) {
        return "Query Error: " . $e->getMessage() . ". SQL: " . $sql . ". Object specified: " . $object;
    }
    // return to the caller
    return $retVal;
    //error_log(print_r($retVal, true));
}

function dumpParticipantWorkFlows($DONOR_CODE)
{
    // include the data access class
    include_once "DAO.php";
    try {
        // create a new data access object
        $db = new DAO();
        // set the SQL
        $sql = "EXEC dbo.GetParticipantWorkFlows @DONOR_CODE ='" . $DONOR_CODE . "'";
        // set the select statement
        $db->setSQL($sql);
        // execute the SQL
        if ($db->execute()) {
            // did we get some rows
            if (sqlsrv_has_rows($db->getResultSet())) {
                // output the table and the first row (column headers)
                echo '<br>';
                echo '<table class="sorted table-autosort:0 table-stripeclass:alternate">';
                echo "<thead><tr>";
                echo "<th class='table-sortable:default' width='70'>Name</th>";
                echo "<th class='table-sortable:default' width='295'>Description</th>";
                echo "<th class='table-sortable:default' width='75'>Status</th>";
                echo "<th class='table-sortable:default' width='150'>Next step</th>";
                echo "<th class='table-sortable:default' width='200'>Next step role</th>";
                echo "</tr></thead><tbody>";
                // output the table rows
                while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                    echo '<tr><td class="left" width="70"><a href="../Participant/History.php?wfID=' . $row['ID'] . '" target="_blank">' . $row['Name'] . '</a></td>';
                    echo '<td class="left" width="295">' . $row['Description'] . '</td>';
                    echo '<td class="center" width="75">' . $row['WorkFlowStatus'] . '</td>';
                    echo '<td class="left" width="150">' . $row['NextStep'] . '</td>';
                    echo '<td class="left" width="200">' . $row['Role'] . '</td></tr>';
                }
                // finish the table
                echo "</tbody></table>";
            } else {
                echo "<div class='err'>No data found.</div>";
            }
        }
    } catch (Exception $e) {
        echo $e->getMessage(), "\n";
    }
    echo "</br>";
}

/**
 * displays the founder pulldown
 *
 * @param unknown_type $ID
 * @param unknown_type $selectedVal
 * @param unknown_type $isBootstrap
 */
function displayFounderPulldown($ID, $selectedVal, $isBootstrap = false, $tooltip = null)
{
    // include the data access class
    include_once "DAO.php";
    try {
        // create a new data access object
        $db = new DAO();
        $sql = "EXEC dbo.GetCompanyLookUp @likeClause=" . $like . "'";
        // set the select statement
        $db->setSQL($sql);
        // execute the SQL
        if ($db->execute()) {
            // did we get some rows
            if (sqlsrv_has_rows($db->getResultSet())) {
                if ($isBootstrap) {
                    // start the pulldown control
                    echo '<select name="' . $ID . '" id="' . $ID . '" class="form-control" data-toggle="tooltip" data-placement="bottom"' . ' title="' . $tooltip . '"><option value="-1">Select a value...</option>';
                    // output the table rows
                    while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                        echo '<option value = "' . $row['CompanyID'] . '" ' . IsSelected($row['CompanyID'], $selectedVal) . '>' . $row['CompanyName'] . '</option>';
                    }
                    // finish off the control
                    echo "</select>";
                } else {
                    // start the pulldown control
                    echo '<select name="' . $ID . '" id="' . $ID . '"><option value="-1">Select a value...</option>';
                    // output the table rows
                    while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                        echo '<option value = "' . $row['CompanyID'] . '" ' . IsSelected($row['CompanyID'], $selectedVal) . '>' . $row['CompanyName'] . '</option>';
                    }
                    // finish off the control
                    echo "</select>";
                }
            } else {
                echo '<div class="err">No data found.</div>';
            }
        }
    } catch (Exception $e) {
        echo $e->getMessage(), "\n";
    }
}

/**
 * gets the participant variants from NCGENES
 *
 * @param string $DonorCode
 */
function doGetParticipantVariants($DonorCode)
{
    // include the data access class
    include_once "DAO.php";
    // create a new data access object
    $db = new DAO();
    // set the request params
    $AnalysisType = 2;
    // the type of analysis results (Dx=2 vs. incidental=1)
    $roleID = 22;
    // the role of the user (22 is admin/everything)
    $type = 1;
    // the type of results (parent rows=1 vs transcript rows=2)
    $geneID = 'No filter';
    // filter on a specific gene in the results
    $FilterID = -1;
    // the type of specific bin analysis result (specific Dx code vs. specific incidental code)
    // create the SQL
    $sql = "EXEC dbo.GetAnalysisResults @DONOR_CODE = '" . $DonorCode . "', @AnalysisType=" . $AnalysisType . ", @Role=" . $roleID . ", @type=" . $type . ", @geneID='" . $geneID . "', @FilterID=" . $FilterID;
    // assign the SQL
    $db->setSQL($sql);
    // preset the return value
    $retVal = NULL;
    // execute the SQL
    if ($db->execute()) {
        // did we get some rows
        if (sqlsrv_has_rows($db->getResultSet())) {
            // init a counter
            $i = 0;
            // output the table rows
            while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                // add the row to the output array
                $data[$i] = $row;
                // next row
                $i++;
            }
            // return the data JSON formatted
            $retVal = '{"data":' . json_encode($data) . '}';
        } else {
            $retVal = '{"error": "doGetParticipantVariants() - No data"}';
        }
    } else {
        $retVal = '{"error": "doGetParticipantVariants() - Error getting data"}';
    }
    //error_log(print_r($data, true));
    // return the data to the caller
    echo $retVal;
    // terminate the data stream
    die;
}

<?php

include_once "DAO.php";
//create a data access object
$dao = new DAO();
//pass the sql statement to the data access object
$dao->setSQL("[dbo].[getcompanies]");
try {
    // run the sql statement
    $dao->execute();
    if (sqlsrv_has_rows($dao->getResultSet())) {
        $rows = sqlsrv_num_rows($dao->getResultSet());
        if ($rows === false) {
            echo "error";
        } else {
            echo 'Number of rows: ' . $rows;
        }
    } else {
        echo "no rows";
    }
} catch (Exception $e) {
    return array(false, "NonQuery Error: " . $e->getMessage() . ". SQL: " . $sql);
}

 /**
  * Gets the list of IDs and roles for the user.
  *
  * @param string $UserName
  */
 function getUserIDRoles($UserName)
 {
     // include the data access class
     include_once "DAO.php";
     // preset the return flag
     $retVal = 0;
     try {
         // save the user name in this object
         $this->setUserName($UserName);
         // create a new data access object
         $db = new DAO();
         // clear the output variable before use
         unset($this->UserIDRoleList);
         // set the stored proc that does the work
         $db->setSQL("exec dbo.GetUserIDRoles @UserName=N'" . $UserName . "'");
         // execute the SQL
         if ($db->execute()) {
             // for each item in the table
             while ($item = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_NUMERIC)) {
                 // save the records for this table
                 $this->UserIDRoleList[] = $item;
             }
         }
     } catch (Exception $e) {
         echo $e->getMessage(), "\n";
         // set an error code
         $retVal = 1;
     }
     // return to the caller
     return $retVal;
 }