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