Clinical trials viewer downloads and parses information from clinicaltrials.gov at the time of search to populate the graph of clinical trials. FDA information is updated weekly from the Drugs@FDA dataset and the FDA postmarketing commitment data set.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 

1152 lines
42 KiB

<?php
function ctv_update_fda_info () {
// Delete the old FDA files
exec ("rm -rdf " . ABS_PATH . "dl/fda/");
// Download fresh ones!
if ( file_put_contents( ABS_PATH . "dl/fda.zip", file_get_contents("https://www.fda.gov/media/89850/download")) ) {
echo "Downloaded FDA data<br>";
mkdir ( ABS_PATH . "dl/fda/");
$zip = new ZipArchive;
$res = $zip->open( ABS_PATH . "dl/fda.zip" );
if ( $res === TRUE ) {
$zip->extractTo( ABS_PATH . "dl/fda/" );
$zip->close();
echo "Extracted FDA zipped folder<br>";
exec ("dos2unix " . ABS_PATH . "dl/fda/*");
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare(
"TRUNCATE TABLE fda_ActionTypes_Lookup; " .
"TRUNCATE TABLE fda_ApplicationDocs; " .
"TRUNCATE TABLE fda_Applications; " .
"TRUNCATE TABLE fda_ApplicationsDocsType_Lookup; " .
"TRUNCATE TABLE fda_MarketingStatus; " .
"TRUNCATE TABLE fda_MarketingStatus_Lookup; " .
"TRUNCATE TABLE fda_Products; " .
"TRUNCATE TABLE fda_SubmissionClass_Lookup; " .
"TRUNCATE TABLE fda_SubmissionPropertyType; " .
"TRUNCATE TABLE fda_Submissions; " .
"TRUNCATE TABLE fda_TE;"
);
if ($stmt->execute()) {
echo "Successfully cleared old FDA data from db<br><br>";
} else {
echo "Error clearing old FDA data<br><br>";
}
$dbh = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
foreach ( glob ( ABS_PATH . "dl/fda/*" ) as $fda_abs_file ) {
echo "Begin loop: " . exec ("wc -l " . $fda_abs_file) . "<br>";
$filename = substr($fda_abs_file, strrpos ($fda_abs_file, "/")+1);
$tablename = substr($filename, 0, strrpos($filename, "."));
$fda_file_handle = fopen($fda_abs_file, "r") or die("Unable to open " . $filename . "<br>");
$table_columns = str_replace("\t", ", ", fgets($fda_file_handle));
fclose($fda_file_handle);
// echo "After read: " . exec ("wc -l " . $fda_abs_file) . "<br>";
echo "Populating table " . $tablename . " ...<br>";
// exec ("iconv -f ISO-8859-1 -t UTF-8 -o " . $fda_abs_file . " " . $fda_abs_file);
// echo "After conversion to UTF-8: " . exec ("wc -l " . $fda_abs_file) . "<br>";
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE));
$stmt = $dbh->prepare("LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `fda_" . $tablename . "` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES (" . $table_columns . ");");
$stmt->bindParam(':file', $file);
$file = $fda_abs_file;
if ( file_exists ($file) ) {
echo "File " . $file . " exists<br>";
} else {
echo "File " . $file . " does not exist<br>";
}
if ($stmt->execute()) {
echo "File " . $file . " has been added to the database<br>";
} else {
echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
echo $stmt->errorCode() . "<br>";
$errorInfo = $stmt->errorInfo();
echo $errorInfo[1] . "<br>";
echo $errorInfo[2] . "<br>";
}
$dbh = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
// echo "End loop: " . exec ("wc -l " . $fda_abs_file) . "<br>";
echo "<br>";
unlink ($fda_abs_file);
}
rmdir (ABS_PATH . "dl/fda/");
} else {
echo "Failure to extract FDA zipped folder<br>";
}
} else {
echo "Failure to download FDA data<br>";
}
unlink (ABS_PATH . "dl/fda.zip");
}
function ctv_update_pmc_info () {
// Delete the old PMC files
exec("rm -rdf " . ABS_PATH . "dl/pmc/");
// Download fresh ones!
if ( file_put_contents( ABS_PATH . "dl/pmc.zip", file_get_contents("https://www.fda.gov/media/71024/download")) ) {
echo "Downloaded PMC data<br>";
mkdir(ABS_PATH . "dl/pmc/");
$zip = new ZipArchive;
$res = $zip->open( ABS_PATH . "dl/pmc.zip" );
if ( $res === TRUE ) {
$zip->extractTo( ABS_PATH . "dl/pmc/");
$zip->close();
echo "Extracted PMC zipped folder<br>";
exec ("dos2unix " . ABS_PATH . "dl/pmc/*.txt");
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare(
"TRUNCATE TABLE pmc_commitments; " .
"TRUNCATE TABLE pmc_cmt_status;"
);
if ($stmt->execute()) {
echo "Successfully cleared old PMC data from db<br><br>";
} else {
echo "Error clearing old PMC data<br><br>";
}
$dbt = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
echo "Populating table pmc_commitments<br>";
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE));
$stmt = $dbh->prepare(
"LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `pmc_commitments` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 0 LINES (`CMT_ID`, `CMT_NUMBER`, `CMT_DOC_TYPE`, `CMT_DOC_TYPE_NO`, `CMT_DESC`, `CMT_STATUS`, `CMT_STATUS_DESC`, `STUDY_TYPE`, @STUDY_START_DATE, @PROTOCOL_SUBMISSION_DATE, @FINAL_RPT_RECV_DATE, @ANNUAL_RPT_DUE_DATE, @ANNUAL_RPT_RECV_DATE, @NDA_BLA_APPROVAL_DATE, @ORIG_PROJ_COMPL_DATE, @CURRENT_PROJ_COMPL_DATE, `NDA_NUMBER`, `APPLICANT`, `PRODUCT`, `PUBLIC_FLAG`, `CDER_OR_CBER`, `SUBPART_FLAG`) SET " .
"`STUDY_START_DATE` = nullif(STR_TO_DATE(@STUDY_START_DATE, '%c/%e/%Y 0:00:00'), @STUDY_START_DATE), " .
"`PROTOCOL_SUBMISSION_DATE` = nullif(STR_TO_DATE(@PROTOCOL_SUBMISSION_DATE, '%c/%e/%Y 0:00:00'), @PROTOCOL_SUBMISSION_DATE), " .
"`FINAL_RPT_RECV_DATE` = nullif(STR_TO_DATE(@FINAL_RPT_RECV_DATE, '%c/%e/%Y 0:00:00'), @FINAL_RPT_RECV_DATE), " .
"`ANNUAL_RPT_DUE_DATE` = nullif(STR_TO_DATE(@ANNUAL_RPT_DUE_DATE, '%c/%e/%Y 0:00:00'), @ANNUAL_RPT_DUE_DATE), " .
"`ANNUAL_RPT_RECV_DATE` = nullif(STR_TO_DATE(@ANNUAL_RPT_RECV_DATE, '%c/%e/%Y 0:00:00'), @ANNUAL_RPT_RECV_DATE), " .
"`NDA_BLA_APPROVAL_DATE` = nullif(STR_TO_DATE(@NDA_BLA_APPROVAL_DATE, '%c/%e/%Y 0:00:00'), @NDA_BLA_APPROVAL_DATE), " .
"`ORIG_PROJ_COMPL_DATE` = nullif(STR_TO_DATE(@ORIG_PROJ_COMPL_DATE, '%c/%e/%Y 0:00:00'), @ORIG_PROJ_COMPL_DATE), " .
"`CURRENT_PROJ_COMPL_DATE` = nullif(STR_TO_DATE(@CURRENT_PROJ_COMPL_DATE, '%c/%e/%Y 0:00:00'), @CURRENT_PROJ_COMPL_DATE)"
);
$stmt->bindParam(':file', $file);
$file = ABS_PATH . "dl/pmc/pmc_commitments.txt";
if ( file_exists ($file) ) {
echo "File " . $file . " exists<br>";
} else {
echo "File " . $file . " does not exist<br>";
}
if ($stmt->execute()) {
echo "File " . $file . " has been added to the database<br>";
} else {
echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
echo $stmt->errorCode() . "<br>";
$errorInfo = $stmt->errorInfo();
echo $errorInfo[1] . "<br>";
echo $errorInfo[2] . "<br>";
}
$dbh = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
echo "<br>Populating table pmc_cmt_status<br>";
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE));
$stmt = $dbh->prepare("LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `pmc_cmt_status` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 0 LINES (`CMT_Status`, `Status_Desc`, `SortOrder`);");
$stmt->bindParam(':file', $file);
$file = ABS_PATH . "dl/pmc/pmc_cmt_status.txt";
if ( file_exists ($file) ) {
echo "File " . $file . " exists<br>";
} else {
echo "File " . $file . " does not exist<br>";
}
if ($stmt->execute()) {
echo "File " . $file . " has been added to the database<br>";
} else {
echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
echo $stmt->errorCode() . "<br>";
$errorInfo = $stmt->errorInfo();
echo $errorInfo[1] . "<br>";
echo $errorInfo[2] . "<br>";
}
$dbh = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
echo "<br>";
foreach ( glob ( ABS_PATH . "dl/pmc/*" ) as $pmc_abs_file ) {
unlink($pmc_abs_file);
}
rmdir(ABS_PATH . "dl/pmc/");
} else {
echo "Failure to extract PMC zipped folder<br>";
}
} else {
echo "Failure to download PMC data<br>";
}
unlink (ABS_PATH . "dl/pmc.zip");
}
function ctv_update_dev_info () {
// Delete the old medical devices file
exec("rm -rdf " . ABS_PATH . "dl/dev/");
// Download a fresh one!
if ( file_put_contents( ABS_PATH . "dl/pma.zip", file_get_contents("http://www.accessdata.fda.gov/premarket/ftparea/pma.zip")) ) {
echo "Downloaded medical devices PMA database<br>";
mkdir (ABS_PATH . "dl/dev/");
$zip = new ZipArchive;
$res = $zip->open( ABS_PATH . "dl/pma.zip" );
if ( $res === TRUE ) {
$zip->extractTo( ABS_PATH . "dl/dev/" );
$zip->close();
echo "Extracted medical devices PMA database<br>";
exec ("dos2unix " . ABS_PATH . "dl/dev/*");
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("TRUNCATE TABLE devices_pma;");
if ($stmt->execute()) {
echo "Successfully cleared old medical devices data from db<br><br>";
} else {
echo "Error clearing old medical devices data<br><br>";
}
$dbh = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
$pma_abs_file = ABS_PATH . "dl/dev/pma.txt";
$filename = substr($pma_abs_file, strrpos ($fda_abs_file, "/")+1);
$pma_file_handle = fopen($pma_abs_file, "r") or die("Unable to open " . $filename . "<br>");
$table_columns = str_replace("|", ", ", fgets($pma_file_handle));
// echo "Columns: " . $table_columns;
fclose($pma_file_handle);
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE));
$stmt = $dbh->prepare(
"LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `devices_pma` " .
"FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES " .
"(PMANUMBER, SUPPLEMENTNUMBER, APPLICANT, STREET_1, STREET_2, CITY, STATE, ZIP, ZIP_EXT, GENERICNAME, TRADENAME, PRODUCTCODE, ADVISORYCOMMITTEE, SUPPLEMENTTYPE, SUPPLEMENTREASON, REVIEWGRANTEDYN, @DATERECEIVED, @DECISIONDATE, DOCKETNUMBER, @FEDREGNOTICEDATE, DECISIONCODE, AOSTATEMENT) " .
"SET `DATERECEIVED` = nullif(STR_TO_DATE(@DATERECEIVED, '%c/%e/%Y'), @DATERECEIVED), " .
"`DECISIONDATE` = nullif(STR_TO_DATE(@DECISIONDATE, '%c/%e/%Y'), @DECISIONDATE), " .
"`FEDREGNOTICEDATE` = nullif(STR_TO_DATE(@FEDREGNOTICEDATE, '%c/%e/%Y'), @FEDREGNOTICEDATE);"
);
$stmt->bindParam(':file', $file);
$file = $pma_abs_file;
if ( file_exists ($file) ) {
echo "File " . $file . " exists<br>";
} else {
echo "File " . $file . " does not exist<br>";
}
if ($stmt->execute()) {
echo "File " . $file . " has been added to the database<br>";
} else {
echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
echo $stmt->errorCode() . "<br>";
$errorInfo = $stmt->errorInfo();
echo $errorInfo[1] . "<br>";
echo $errorInfo[2] . "<br>";
}
$dbh = null;
} catch (PDOException $e) {
echo $e->getMessage();
}
} else {
echo "Failure to extract medical devices PMA database<br>";
}
} else {
echo "Failure to download medical devices PMA database<br>";
}
unlink(ABS_PATH . "dl/pma.zip");
}
function ctv_query_ct_dot_gov ( $query, $indication, $paeds ) {
// Make a new address
$haveuniqueaddress = 0;
while ( $haveuniqueaddress == 0 ) {
$address = md5(uniqid(rand(), true));
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `address` FROM `ctviewers` WHERE `address` = :address LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
if ( count ($result) == 0 ) {
$haveuniqueaddress = 1;
}
}
catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
$query = urlencode ($query);
$indication = urlencode ($indication);
if ( $_POST['indication'] == "" ) { // If no indication is entered
$indication_url = "";
} else {
$indication_url = "cond=" . $indication . "&";
}
if ( $paeds == "false" ) { // Paeds box not checked
$ct_dot_gov_url = "https://clinicaltrials.gov/ct2/results/download_fields?down_count=10000&down_flds=all&down_fmt=tsv&" . $indication_url . "intr=" . $query . "&flds=a&flds=b&flds=y";
} else { // Paeds box checked
$ct_dot_gov_url = "https://clinicaltrials.gov/ct2/results/download_fields?down_count=10000&down_flds=all&down_fmt=tsv&" . $indication_url . "intr=" . $query . "&age=0&flds=a&flds=b&flds=y";
}
if ( file_put_contents( ABS_PATH . "dl/" . $address . ".tsv", file_get_contents($ct_dot_gov_url)) ) {
// convert to unix
exec ("dos2unix " . ABS_PATH . "dl/" . $address . ".tsv");
// make a new viewer in the ctviewers table
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
if ( $indication == "" ) {
$stmt = $dbh->prepare("INSERT INTO `ctviewers` (`address`, `query`, `paeds`) VALUES (:address, :query, :paeds);");
} else {
$stmt = $dbh->prepare("INSERT INTO `ctviewers` (`address`, `query`, `indication`, `paeds`) VALUES (:address, :query, :indication, :paeds);");
}
$stmt->bindParam(':address', $addy);
$stmt->bindParam(':query', $quer);
if ( $indication != "" ) {
$stmt->bindParam(':indication', $indi);
}
$stmt->bindParam(':paeds', $pae);
$quer = $query;
$indi = $indication;
$addy = $address;
if ( $paeds == "true" ) {
$pae = 1;
} else {
$pae = 0;
}
if (! $stmt->execute()) {
echo "Unable to insert new viewer into database<br>";
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
// insert trial data into ctg_trials table
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE));
$stmt = $dbh->prepare(
"LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `ctg_trials` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES " .
"(`Rank`, `NCT_Number`, `Title`, `Acronym`, `Status`, `Study_Results`, `Conditions`, `Interventions`, `Outcome_Measures`, `Sponsor_Collaborators`, `Gender`, `Age`, `Phases`, `Enrollment`, `Funded_Bys`, `Study_Type`, `Study_Designs`, `Other_IDs`, @Start_Date, @Primary_Completion_Date, @Completion_Date, @First_Posted, @Results_First_Posted, @Last_Update_Posted, `Locations`, `Study_Documents`, `URL`) " .
"SET `address` = :address, " .
"`Start_Date` = CASE WHEN @Start_Date = '' THEN NULL WHEN STR_TO_DATE(@Start_Date, '%M %e, %Y') IS NOT NULL THEN STR_TO_DATE(@Start_Date, '%M %e, %Y') WHEN STR_TO_DATE(@Start_Date, '%M %Y') IS NOT NULL THEN STR_TO_DATE(@Start_Date, '%M %Y') ELSE NULL END, " .
"`Primary_Completion_Date` = CASE WHEN @Primary_Completion_Date = '' THEN NULL WHEN STR_TO_DATE(@Primary_Completion_Date, '%M %e, %Y') IS NOT NULL THEN STR_TO_DATE(@Primary_Completion_Date, '%M %e, %Y') WHEN STR_TO_DATE(@Primary_Completion_Date, '%M %Y') IS NOT NULL THEN STR_TO_DATE(@Primary_Completion_Date, '%M %Y') ELSE NULL END, " .
"`Completion_Date` = CASE WHEN @Completion_Date = '' THEN NULL WHEN STR_TO_DATE(@Completion_Date, '%M %e, %Y') IS NOT NULL THEN STR_TO_DATE(@Completion_Date, '%M %e, %Y') WHEN STR_TO_DATE(@Completion_Date, '%M %Y') IS NOT NULL THEN STR_TO_DATE(@Completion_Date, '%M %Y') ELSE NULL END, " .
"`First_Posted` = CASE WHEN @First_Posted = '' THEN NULL WHEN STR_TO_DATE(@First_Posted, '%M %e, %Y') IS NOT NULL THEN STR_TO_DATE(@First_Posted, '%M %e, %Y') WHEN STR_TO_DATE(@First_Posted, '%M %Y') IS NOT NULL THEN STR_TO_DATE(@First_Posted, '%M %Y') ELSE NULL END, " .
"`Results_First_Posted` = CASE WHEN @Results_First_Posted = '' THEN NULL WHEN STR_TO_DATE(@Results_First_Posted, '%M %e, %Y') IS NOT NULL THEN STR_TO_DATE(@Results_First_Posted, '%M %e, %Y') WHEN STR_TO_DATE(@Results_First_Posted, '%M %Y') IS NOT NULL THEN STR_TO_DATE(@Results_First_Posted, '%M %Y') ELSE NULL END, " .
"`Last_Update_Posted` = CASE WHEN @Last_Update_Posted = '' THEN NULL WHEN STR_TO_DATE(@Last_Update_Posted, '%M %e, %Y') IS NOT NULL THEN STR_TO_DATE(@Last_Update_Posted, '%M %e, %Y') WHEN STR_TO_DATE(@Last_Update_Posted, '%M %Y') IS NOT NULL THEN STR_TO_DATE(@Last_Update_Posted, '%M %Y') ELSE NULL END"
);
$stmt->bindParam(':file', $file);
$stmt->bindParam(':address', $addy);
$addy = $address;
$file = ABS_PATH . "dl/" . $address . ".tsv";
if ($stmt->execute() ) {
echo $address;
} else {
echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
echo $stmt->errorCode() . "<br>";
$errorInfo = $stmt->errorInfo();
echo $errorInfo[1] . "<br>";
echo $errorInfo[2] . "<br>";
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
unlink (ABS_PATH . "dl/" . $address . ".tsv");
} else {
echo "FALSE";
}
}
function ctv_get_ctviewer_for_address ( $address ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `ctviewers` WHERE `address` = :address");
$stmt->bindParam(':address', $addy);
$addy = $address;
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result[0];
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_trials_for_address ( $address ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT *, COALESCE(`Primary_Completion_Date`, `Completion_Date`) as `End_Date` FROM `ctg_trials` WHERE `address` = :address ORDER BY `Start_Date`;");
$stmt->bindParam(':address', $addy);
$addy = $address;
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_applications_for_query ( $query ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Products`, `fda_Applications` WHERE (`fda_Products`.`ApplNo` = `fda_Applications`.`ApplNo`) AND (`DrugName` LIKE :query OR `ActiveIngredient` LIKE :query) GROUP BY `fda_Products`.`ApplNo`");
$stmt->bindParam(':query', $quer);
$quer = "%" . $query . "%";
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_devices_for_query ( $query ) {
$query = urldecode ($query);
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `devices_pma` WHERE `GENERICNAME` LIKE :query OR `TRADENAME` LIKE :query ORDER BY `DECISIONDATE`;");
$stmt->bindParam(':query', $quer);
$quer = "%" . urldecode ($query) . "%";
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_apps_and_products_for_query ( $query ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Products`, `fda_Applications`, `fda_MarketingStatus`, `fda_MarketingStatus_Lookup` WHERE (`fda_Products`.`ApplNo` = `fda_Applications`.`ApplNo`) AND (`DrugName` LIKE :query OR `ActiveIngredient` LIKE :query) AND (`fda_Products`.`ApplNo` = `fda_MarketingStatus`.`ApplNo` and `fda_Products`.`ProductNo` = `fda_MarketingStatus`.`ProductNo`) AND (`fda_MarketingStatus`.`MarketingStatusID` = `fda_MarketingStatus_Lookup`.`MarketingStatusID`)");
$stmt->bindParam(':query', $quer);
$quer = "%" . $query . "%";
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_apps_and_subs_for_query ( $query ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Products`, `fda_Applications`, `fda_Submissions`, `fda_SubmissionClass_Lookup` WHERE (`fda_Products`.`ApplNo` = `fda_Applications`.`ApplNo`) AND (`DrugName` LIKE :query OR `ActiveIngredient` LIKE :query) AND (`fda_Submissions`.`ApplNo` = `fda_Products`.`ApplNo`) AND (`fda_Submissions`.`SubmissionClassCodeID` = `fda_SubmissionClass_Lookup`.`SubmissionClassCodeID`) GROUP BY `fda_Submissions`.`ApplNo`, `fda_Submissions`.`SubmissionType`, `fda_Submissions`.`SubmissionNo` ORDER BY `fda_Submissions`.`SubmissionStatusDate`");
$stmt->bindParam(':query', $quer);
$quer = "%" . $query . "%";
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_apps_and_docs_for_query ( $query ) { // ***
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Products`, `fda_Applications`, `fda_ApplicationDocs`, `fda_ApplicationsDocsType_Lookup` WHERE (`fda_Products`.`ApplNo` = `fda_Applications`.`ApplNo`) AND (`fda_Products`.`DrugName` LIKE :query OR `fda_Products`.`ActiveIngredient` LIKE :query) AND (`fda_ApplicationDocs`.`ApplNo` = `fda_Products`.`ApplNo`) AND (`fda_ApplicationDocs`.`ApplicationDocsTypeID` = `fda_ApplicationsDocsType_Lookup`.`ApplicationDocsType_Lookup_ID`) GROUP BY `fda_ApplicationDocs`.`ApplicationDocsURL` ORDER BY `fda_ApplicationDocs`.`ApplicationDocsDate`");
$stmt->bindParam(':query', $quer);
$quer = "%" . $query . "%";
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_products_for_application ( $applno ) { // This function may no longer be needed
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Products`, `fda_Applications`, `fda_MarketingStatus`, `fda_MarketingStatus_Lookup` WHERE (`fda_Products`.`ApplNo` = :applno) AND `fda_Products`.`ApplNo` = `fda_Applications`.`ApplNo` AND (`fda_Products`.`ApplNo` = `fda_MarketingStatus`.`ApplNo` and `fda_Products`.`ProductNo` = `fda_MarketingStatus`.`ProductNo`) AND (`fda_MarketingStatus`.`MarketingStatusID` = `fda_MarketingStatus_Lookup`.`MarketingStatusID`)");
$stmt->bindParam(':applno', $an);
$an = $applno;
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_documents_for_product ( $applno ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_ApplicationDocs`, `fda_ApplicationsDocsType_Lookup` WHERE ApplNo = :applno AND (`fda_ApplicationDocs`.`ApplicationDocsTypeID` = `fda_ApplicationsDocsType_Lookup`.`ApplicationDocsType_Lookup_ID`) ORDER BY `ApplicationDocsDate`");
$stmt->bindParam(':applno', $an);
$an = $applno;
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_submissions_for_product ( $applno ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Submissions`, `fda_SubmissionClass_Lookup` WHERE `ApplNo` = :applno AND (`fda_Submissions`.`SubmissionClassCodeID` = `fda_SubmissionClass_Lookup`.`SubmissionClassCodeID`)");
$stmt->bindParam(':applno', $an);
$an = $applno;
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_first_start_for_address ( $address, $ctviewer ) {
// get the first start date
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `Start_Date` FROM `ctg_trials` WHERE `address` = :address AND `Start_Date` IS NOT NULL ORDER BY `Start_Date` ASC LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_start = $result[0]['Start_Date'];
$all_dates[] = $first_start;
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first primary completion date
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `Primary_Completion_Date` FROM `ctg_trials` WHERE `address` = :address AND `Primary_Completion_Date` IS NOT NULL ORDER BY `Primary_Completion_Date` ASC LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_primary_completion = $result[0]['Primary_Completion_Date'];
if ( count($result) > 0 && $first_primary_completion != "0000-00-00" ) {
$all_dates[] = $first_primary_completion;
}
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first completion date
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `Completion_Date` FROM `ctg_trials` WHERE `address` = :address AND `Completion_Date` IS NOT NULL ORDER BY `Completion_Date` ASC LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_completion = $result[0]['Completion_Date'];
if ( count($result) > 0 && $first_completion != "0000-00-00" ) {
$all_dates[] = $first_completion;
}
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first date from fda submissions
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `SubmissionStatusDate` FROM `fda_Products`, `fda_Submissions` WHERE `SubmissionStatusDate` IS NOT NULL AND (`fda_Products`.`DrugName` LIKE :query OR `fda_Products`.`ActiveIngredient` LIKE :query) AND `fda_Products`.`ApplNo` = `fda_Submissions`.`ApplNo` ORDER BY `SubmissionStatusDate` ASC LIMIT 1");
$stmt->bindParam(':query', $quer);
$quer = "%" . $ctviewer['query'] . "%";
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_fda_submission = substr($result[0]['SubmissionStatusDate'], 0, 10);
if ( count($result) > 0 && $first_fda_submission != "0000-00-00" ) {
$all_dates[] = $first_fda_submission;
}
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first date from fda application docs
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `ApplicationDocsDate` FROM `fda_Products`, `fda_ApplicationDocs` WHERE `ApplicationDocsDate` IS NOT NULL AND (`fda_Products`.`DrugName` LIKE :query OR `fda_Products`.`ActiveIngredient` LIKE :query) AND `fda_Products`.`ApplNo` = `fda_ApplicationDocs`.`ApplNo` ORDER BY `ApplicationDocsDate` ASC LIMIT 1");
$stmt->bindParam(':query', $quer);
$quer = "%" . $ctviewer['query'] . "%";
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_fda_application_doc = substr($result[0]['ApplicationDocsDate'], 0, 10);
if ( count($result) > 0 && $first_fda_application_doc != "0000-00-00" ) {
$all_dates[] = $first_fda_application_doc;
}
} catch (PDOException $e) {
echo $e->getMessage();
}
// get date from medical device PMA's
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `DECISIONDATE` FROM `devices_pma` WHERE `DECISIONDATE` IS NOT NULL AND (`GENERICNAME` LIKE :query OR `TRADENAME` LIKE :query) ORDER BY `DECISIONDATE` ASC LIMIT 1");
$stmt->bindParam(':query', $quer);
$quer = "%" . urldecode($ctviewer['query']) . "%";
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_device_pma = substr($result[0]['DECISIONDATE'], 0, 10);
if ( count($result) > 0 && $first_device_pma != "0000-00-00" ) {
$all_dates[] = $first_device_pma;
}
} catch (PDOException $e) {
echo $e->getMessage();
}
$first_date = min($all_dates);
return substr($first_date, 0, 7) . "-01";
}
function ctv_get_last_end_for_address ( $address, $ctviewer ) {
// get the first start date
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `Start_Date` FROM `ctg_trials` WHERE `address` = :address AND `Start_Date` IS NOT NULL ORDER BY `Start_Date` DESC LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$last_start = $result[0]['Start_Date'];
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first primary completion date
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `Primary_Completion_Date` FROM `ctg_trials` WHERE `address` = :address AND `Primary_Completion_Date` IS NOT NULL ORDER BY `Primary_Completion_Date` DESC LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$last_primary_completion = $result[0]['Primary_Completion_Date'];
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first completion date
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `Completion_Date` FROM `ctg_trials` WHERE `address` = :address AND `Completion_Date` IS NOT NULL ORDER BY `Completion_Date` DESC LIMIT 1");
$stmt->bindParam(':address', $addy);
$addy = $address;
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$last_completion = $result[0]['Completion_Date'];
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first date from fda submissions
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `SubmissionStatusDate` FROM `fda_Products`, `fda_Submissions` WHERE (`fda_Products`.`DrugName` LIKE :query OR `fda_Products`.`ActiveIngredient` LIKE :query) AND `fda_Products`.`ApplNo` = `fda_Submissions`.`ApplNo` ORDER BY `SubmissionStatusDate` DESC LIMIT 1");
$stmt->bindParam(':query', $quer);
$quer = "%" . $ctviewer['query'] . "%";
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$last_fda_submission = substr($result[0]['SubmissionStatusDate'], 0, 10);
} catch (PDOException $e) {
echo $e->getMessage();
}
// get first date from fda application docs
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `ApplicationDocsDate` FROM `fda_Products`, `fda_ApplicationDocs` WHERE (`fda_Products`.`DrugName` LIKE :query OR `fda_Products`.`ActiveIngredient` LIKE :query) AND `fda_Products`.`ApplNo` = `fda_ApplicationDocs`.`ApplNo` ORDER BY `ApplicationDocsDate` DESC LIMIT 1");
$stmt->bindParam(':query', $quer);
$quer = "%" . $ctviewer['query'] . "%";
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$last_fda_application_doc = substr($result[0]['ApplicationDocsDate'], 0, 10);
} catch (PDOException $e) {
echo $e->getMessage();
}
// get date from medical device PMA's
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT `DECISIONDATE` FROM `devices_pma` WHERE `DECISIONDATE` IS NOT NULL AND (`GENERICNAME` LIKE :query OR `TRADENAME` LIKE :query) ORDER BY `DECISIONDATE` ASC LIMIT 1");
$stmt->bindParam(':query', $quer);
$quer = "%" . $ctviewer['query'] . "%";
$stmt->execute();
$result = $stmt->fetchAll();
$dbh = null;
$first_device_pma = substr($result[0]['DECISIONDATE'], 0, 10);
} catch (PDOException $e) {
echo $e->getMessage();
}
$last_date = max($last_start, $last_primary_completion, $last_completion, $last_fda_submission, $last_fda_application_doc, $first_device_pma);
return substr($last_date, 0, 7) . "-01";
}
function ctv_get_width_of_display ($start, $end) {
$function_name = "ctg_get_width_of_display";
$startyear = substr ($start, 0, 4);
$endyear = substr ($end, 0, 4);
$difference = $endyear - $startyear + 1;
$width = 120 * $difference;
if ( $width < 940) {
return 940;
} else {
return $width;
}
}
function ctv_sort_by ( $sortcolumn, $address ) {
// Supported values for $sortcolumn are:
// Start_Date, End_Date, Funding, Phase
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("UPDATE `ctviewers` SET `sort_by` = :sort WHERE `address` = :address LIMIT 1;");
$stmt->bindParam(':sort', $sort);
$stmt->bindParam(':address', $addy);
$sort = $sortcolumn;
$addy = $address;
if ($stmt->execute()) {
return TRUE;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_pmc_for_nda ( $nda_number ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `pmc_commitments`, `pmc_cmt_status` WHERE (`pmc_commitments`.`CMT_STATUS` = `pmc_cmt_status`.`CMT_Status`) AND (`NDA_NUMBER` = :nda) ");
$stmt->bindParam(':nda', $nda);
$nda = $nda_number;
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
function ctv_get_apps_and_pmcs_for_query ( $query ) {
try {
$dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$stmt = $dbh->prepare("SELECT * FROM `fda_Products`, `fda_Applications`, `pmc_commitments`, `pmc_cmt_status` WHERE (`fda_Products`.`ApplNo` = `fda_Applications`.`ApplNo`) AND (`DrugName` LIKE :query OR `ActiveIngredient` LIKE :query) AND (`pmc_commitments`.`NDA_NUMBER` = `fda_Products`.`ApplNo`) AND (`pmc_commitments`.`CMT_STATUS` = `pmc_cmt_status`.`CMT_Status`) GROUP BY `CMT_ID`");
$stmt->bindParam(':query', $quer);
$quer = "%" . $query . "%";
if ($stmt->execute()) {
$result = $stmt->fetchAll();
return $result;
} else {
return FALSE;
}
} catch (PDOException $e) {
echo $e->getMessage() . "<br>";
}
}
?>