You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1153 lines
42 KiB
PHP
1153 lines
42 KiB
PHP
<?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 . "` CHARACTER SET latin1 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/");
|
|
$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` CHARACTER SET latin1 FIELDS TERMINATED BY '~' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 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, `FINAL_REPORT_DUE_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), " .
|
|
"`FINAL_REPORT_DUE_DATE` = nullif(STR_TO_DATE(@ORIG_PROJ_COMPL_DATE, '%c/%e/%Y 0:00:00'), @FINAL_REPORT_DUE_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` CHARACTER SET latin1 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>";
|
|
}
|
|
|
|
}
|
|
|
|
?>
|