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.

984 lines
36KB

  1. <?php
  2. function ctv_update_fda_info () {
  3. // Delete the old FDA files
  4. exec ("rm -rdf " . ABS_PATH . "dl/fda/");
  5. // Download fresh ones!
  6. if ( file_put_contents( ABS_PATH . "dl/fda.zip", file_get_contents("https://www.fda.gov/media/89850/download")) ) {
  7. echo "Downloaded FDA data<br>";
  8. mkdir ( ABS_PATH . "dl/fda/");
  9. $zip = new ZipArchive;
  10. $res = $zip->open( ABS_PATH . "dl/fda.zip" );
  11. if ( $res === TRUE ) {
  12. $zip->extractTo( ABS_PATH . "dl/fda/" );
  13. $zip->close();
  14. echo "Extracted FDA zipped folder<br>";
  15. exec ("dos2unix " . ABS_PATH . "dl/fda/*");
  16. try {
  17. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  18. $stmt = $dbh->prepare(
  19. "TRUNCATE TABLE fda_ActionTypes_Lookup; " .
  20. "TRUNCATE TABLE fda_ApplicationDocs; " .
  21. "TRUNCATE TABLE fda_Applications; " .
  22. "TRUNCATE TABLE fda_ApplicationsDocsType_Lookup; " .
  23. "TRUNCATE TABLE fda_MarketingStatus; " .
  24. "TRUNCATE TABLE fda_MarketingStatus_Lookup; " .
  25. "TRUNCATE TABLE fda_Products; " .
  26. "TRUNCATE TABLE fda_SubmissionClass_Lookup; " .
  27. "TRUNCATE TABLE fda_SubmissionPropertyType; " .
  28. "TRUNCATE TABLE fda_Submissions; " .
  29. "TRUNCATE TABLE fda_TE;"
  30. );
  31. if ($stmt->execute()) {
  32. echo "Successfully cleared old FDA data from db<br><br>";
  33. } else {
  34. echo "Error clearing old FDA data<br><br>";
  35. }
  36. $dbh = null;
  37. } catch (PDOException $e) {
  38. echo $e->getMessage();
  39. }
  40. foreach ( glob ( ABS_PATH . "dl/fda/*" ) as $fda_abs_file ) {
  41. echo "Begin loop: " . exec ("wc -l " . $fda_abs_file) . "<br>";
  42. $filename = substr($fda_abs_file, strrpos ($fda_abs_file, "/")+1);
  43. $tablename = substr($filename, 0, strrpos($filename, "."));
  44. $fda_file_handle = fopen($fda_abs_file, "r") or die("Unable to open " . $filename . "<br>");
  45. $table_columns = str_replace("\t", ", ", fgets($fda_file_handle));
  46. fclose($fda_file_handle);
  47. // echo "After read: " . exec ("wc -l " . $fda_abs_file) . "<br>";
  48. echo "Populating table " . $tablename . " ...<br>";
  49. // exec ("iconv -f ISO-8859-1 -t UTF-8 -o " . $fda_abs_file . " " . $fda_abs_file);
  50. // echo "After conversion to UTF-8: " . exec ("wc -l " . $fda_abs_file) . "<br>";
  51. try {
  52. $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));
  53. $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 . ");");
  54. $stmt->bindParam(':file', $file);
  55. $file = $fda_abs_file;
  56. if ( file_exists ($file) ) {
  57. echo "File " . $file . " exists<br>";
  58. } else {
  59. echo "File " . $file . " does not exist<br>";
  60. }
  61. if ($stmt->execute()) {
  62. echo "File " . $file . " has been added to the database<br>";
  63. } else {
  64. echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
  65. echo $stmt->errorCode() . "<br>";
  66. $errorInfo = $stmt->errorInfo();
  67. echo $errorInfo[1] . "<br>";
  68. echo $errorInfo[2] . "<br>";
  69. }
  70. $dbh = null;
  71. } catch (PDOException $e) {
  72. echo $e->getMessage();
  73. }
  74. // echo "End loop: " . exec ("wc -l " . $fda_abs_file) . "<br>";
  75. echo "<br>";
  76. unlink ($fda_abs_file);
  77. }
  78. rmdir (ABS_PATH . "dl/fda/");
  79. } else {
  80. echo "Failure to extract FDA zipped folder<br>";
  81. }
  82. } else {
  83. echo "Failure to download FDA data<br>";
  84. }
  85. unlink (ABS_PATH . "dl/fda.zip");
  86. }
  87. function ctv_update_pmc_info () {
  88. // Delete the old PMC files
  89. exec("rm -rdf " . ABS_PATH . "dl/pmc/");
  90. // Download fresh ones!
  91. if ( file_put_contents( ABS_PATH . "dl/pmc.zip", file_get_contents("https://www.fda.gov/media/71024/download")) ) {
  92. echo "Downloaded PMC data<br>";
  93. mkdir(ABS_PATH . "dl/pmc/");
  94. $zip = new ZipArchive;
  95. $res = $zip->open( ABS_PATH . "dl/pmc.zip" );
  96. if ( $res === TRUE ) {
  97. $zip->extractTo( ABS_PATH . "dl/pmc/");
  98. $zip->close();
  99. echo "Extracted PMC zipped folder<br>";
  100. exec ("dos2unix " . ABS_PATH . "dl/pmc/*.txt");
  101. try {
  102. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  103. $stmt = $dbh->prepare(
  104. "TRUNCATE TABLE pmc_commitments; " .
  105. "TRUNCATE TABLE pmc_cmt_status;"
  106. );
  107. if ($stmt->execute()) {
  108. echo "Successfully cleared old PMC data from db<br><br>";
  109. } else {
  110. echo "Error clearing old PMC data<br><br>";
  111. }
  112. $dbt = null;
  113. } catch (PDOException $e) {
  114. echo $e->getMessage();
  115. }
  116. echo "Populating table pmc_commitments<br>";
  117. try {
  118. $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));
  119. $stmt = $dbh->prepare(
  120. "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 " .
  121. "`STUDY_START_DATE` = nullif(STR_TO_DATE(@STUDY_START_DATE, '%c/%e/%Y 0:00:00'), @STUDY_START_DATE), " .
  122. "`PROTOCOL_SUBMISSION_DATE` = nullif(STR_TO_DATE(@PROTOCOL_SUBMISSION_DATE, '%c/%e/%Y 0:00:00'), @PROTOCOL_SUBMISSION_DATE), " .
  123. "`FINAL_RPT_RECV_DATE` = nullif(STR_TO_DATE(@FINAL_RPT_RECV_DATE, '%c/%e/%Y 0:00:00'), @FINAL_RPT_RECV_DATE), " .
  124. "`ANNUAL_RPT_DUE_DATE` = nullif(STR_TO_DATE(@ANNUAL_RPT_DUE_DATE, '%c/%e/%Y 0:00:00'), @ANNUAL_RPT_DUE_DATE), " .
  125. "`ANNUAL_RPT_RECV_DATE` = nullif(STR_TO_DATE(@ANNUAL_RPT_RECV_DATE, '%c/%e/%Y 0:00:00'), @ANNUAL_RPT_RECV_DATE), " .
  126. "`NDA_BLA_APPROVAL_DATE` = nullif(STR_TO_DATE(@NDA_BLA_APPROVAL_DATE, '%c/%e/%Y 0:00:00'), @NDA_BLA_APPROVAL_DATE), " .
  127. "`ORIG_PROJ_COMPL_DATE` = nullif(STR_TO_DATE(@ORIG_PROJ_COMPL_DATE, '%c/%e/%Y 0:00:00'), @ORIG_PROJ_COMPL_DATE), " .
  128. "`CURRENT_PROJ_COMPL_DATE` = nullif(STR_TO_DATE(@CURRENT_PROJ_COMPL_DATE, '%c/%e/%Y 0:00:00'), @CURRENT_PROJ_COMPL_DATE)"
  129. );
  130. $stmt->bindParam(':file', $file);
  131. $file = ABS_PATH . "dl/pmc/pmc_commitments.txt";
  132. if ( file_exists ($file) ) {
  133. echo "File " . $file . " exists<br>";
  134. } else {
  135. echo "File " . $file . " does not exist<br>";
  136. }
  137. if ($stmt->execute()) {
  138. echo "File " . $file . " has been added to the database<br>";
  139. } else {
  140. echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
  141. echo $stmt->errorCode() . "<br>";
  142. $errorInfo = $stmt->errorInfo();
  143. echo $errorInfo[1] . "<br>";
  144. echo $errorInfo[2] . "<br>";
  145. }
  146. $dbh = null;
  147. } catch (PDOException $e) {
  148. echo $e->getMessage();
  149. }
  150. echo "<br>Populating table pmc_cmt_status<br>";
  151. try {
  152. $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));
  153. $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`);");
  154. $stmt->bindParam(':file', $file);
  155. $file = ABS_PATH . "dl/pmc/pmc_cmt_status.txt";
  156. if ( file_exists ($file) ) {
  157. echo "File " . $file . " exists<br>";
  158. } else {
  159. echo "File " . $file . " does not exist<br>";
  160. }
  161. if ($stmt->execute()) {
  162. echo "File " . $file . " has been added to the database<br>";
  163. } else {
  164. echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
  165. echo $stmt->errorCode() . "<br>";
  166. $errorInfo = $stmt->errorInfo();
  167. echo $errorInfo[1] . "<br>";
  168. echo $errorInfo[2] . "<br>";
  169. }
  170. $dbh = null;
  171. } catch (PDOException $e) {
  172. echo $e->getMessage();
  173. }
  174. echo "<br>";
  175. foreach ( glob ( ABS_PATH . "dl/pmc/*" ) as $pmc_abs_file ) {
  176. unlink($pmc_abs_file);
  177. }
  178. rmdir(ABS_PATH . "dl/pmc/");
  179. } else {
  180. echo "Failure to extract PMC zipped folder<br>";
  181. }
  182. } else {
  183. echo "Failure to download PMC data<br>";
  184. }
  185. unlink (ABS_PATH . "dl/pmc.zip");
  186. }
  187. function ctv_query_ct_dot_gov ( $query, $indication, $paeds ) {
  188. // Make a new address
  189. $haveuniqueaddress = 0;
  190. while ( $haveuniqueaddress == 0 ) {
  191. $address = md5(uniqid(rand(), true));
  192. try {
  193. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  194. $stmt = $dbh->prepare("SELECT `address` FROM `ctviewers` WHERE `address` = :address LIMIT 1");
  195. $stmt->bindParam(':address', $addy);
  196. $addy = $address;
  197. $stmt->execute();
  198. $result = $stmt->fetchAll();
  199. $dbh = null;
  200. if ( count ($result) == 0 ) {
  201. $haveuniqueaddress = 1;
  202. }
  203. }
  204. catch (PDOException $e) {
  205. echo $e->getMessage() . "<br>";
  206. }
  207. }
  208. $query = urlencode ($query);
  209. $indication = urlencode ($indication);
  210. if ( $_POST['indication'] == "" ) { // If no indication is entered
  211. $indication_url = "";
  212. } else {
  213. $indication_url = "cond=" . $indication . "&";
  214. }
  215. if ( $paeds == "false" ) { // Paeds box not checked
  216. $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";
  217. } else { // Paeds box checked
  218. $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";
  219. }
  220. if ( file_put_contents( ABS_PATH . "dl/" . $address . ".tsv", file_get_contents($ct_dot_gov_url)) ) {
  221. // convert to unix
  222. exec ("dos2unix " . ABS_PATH . "dl/" . $address . ".tsv");
  223. // make a new viewer in the ctviewers table
  224. try {
  225. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  226. if ( $indication == "" ) {
  227. $stmt = $dbh->prepare("INSERT INTO `ctviewers` (`address`, `query`, `paeds`) VALUES (:address, :query, :paeds);");
  228. } else {
  229. $stmt = $dbh->prepare("INSERT INTO `ctviewers` (`address`, `query`, `indication`, `paeds`) VALUES (:address, :query, :indication, :paeds);");
  230. }
  231. $stmt->bindParam(':address', $addy);
  232. $stmt->bindParam(':query', $quer);
  233. if ( $indication != "" ) {
  234. $stmt->bindParam(':indication', $indi);
  235. }
  236. $stmt->bindParam(':paeds', $pae);
  237. $quer = $query;
  238. $indi = $indication;
  239. $addy = $address;
  240. if ( $paeds == "true" ) {
  241. $pae = 1;
  242. } else {
  243. $pae = 0;
  244. }
  245. if (! $stmt->execute()) {
  246. echo "Unable to insert new viewer into database<br>";
  247. }
  248. } catch (PDOException $e) {
  249. echo $e->getMessage() . "<br>";
  250. }
  251. // insert trial data into ctg_trials table
  252. try {
  253. $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));
  254. $stmt = $dbh->prepare(
  255. "LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `ctg_trials` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES " .
  256. "(`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`) " .
  257. "SET `address` = :address, " .
  258. "`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, " .
  259. "`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, " .
  260. "`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, " .
  261. "`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, " .
  262. "`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, " .
  263. "`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"
  264. );
  265. $stmt->bindParam(':file', $file);
  266. $stmt->bindParam(':address', $addy);
  267. $addy = $address;
  268. $file = ABS_PATH . "dl/" . $address . ".tsv";
  269. if ($stmt->execute() ) {
  270. echo $address;
  271. } else {
  272. echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
  273. echo $stmt->errorCode() . "<br>";
  274. $errorInfo = $stmt->errorInfo();
  275. echo $errorInfo[1] . "<br>";
  276. echo $errorInfo[2] . "<br>";
  277. }
  278. } catch (PDOException $e) {
  279. echo $e->getMessage() . "<br>";
  280. }
  281. unlink (ABS_PATH . "dl/" . $address . ".tsv");
  282. } else {
  283. echo "FALSE";
  284. }
  285. }
  286. function ctv_get_ctviewer_for_address ( $address ) {
  287. try {
  288. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  289. $stmt = $dbh->prepare("SELECT * FROM `ctviewers` WHERE `address` = :address");
  290. $stmt->bindParam(':address', $addy);
  291. $addy = $address;
  292. if ($stmt->execute()) {
  293. $result = $stmt->fetchAll();
  294. return $result[0];
  295. } else {
  296. return FALSE;
  297. }
  298. } catch (PDOException $e) {
  299. echo $e->getMessage() . "<br>";
  300. }
  301. }
  302. function ctv_get_trials_for_address ( $address ) {
  303. try {
  304. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  305. $stmt = $dbh->prepare("SELECT *, COALESCE(`Primary_Completion_Date`, `Completion_Date`) as `End_Date` FROM `ctg_trials` WHERE `address` = :address ORDER BY `Start_Date`;");
  306. $stmt->bindParam(':address', $addy);
  307. $addy = $address;
  308. if ($stmt->execute()) {
  309. $result = $stmt->fetchAll();
  310. return $result;
  311. } else {
  312. return FALSE;
  313. }
  314. } catch (PDOException $e) {
  315. echo $e->getMessage() . "<br>";
  316. }
  317. }
  318. function ctv_get_applications_for_query ( $query ) {
  319. try {
  320. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  321. $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`");
  322. $stmt->bindParam(':query', $quer);
  323. $quer = "%" . $query . "%";
  324. if ($stmt->execute()) {
  325. $result = $stmt->fetchAll();
  326. return $result;
  327. } else {
  328. return FALSE;
  329. }
  330. } catch (PDOException $e) {
  331. echo $e->getMessage() . "<br>";
  332. }
  333. }
  334. function ctv_get_apps_and_products_for_query ( $query ) {
  335. try {
  336. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  337. $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`)");
  338. $stmt->bindParam(':query', $quer);
  339. $quer = "%" . $query . "%";
  340. if ($stmt->execute()) {
  341. $result = $stmt->fetchAll();
  342. return $result;
  343. } else {
  344. return FALSE;
  345. }
  346. } catch (PDOException $e) {
  347. echo $e->getMessage() . "<br>";
  348. }
  349. }
  350. function ctv_get_apps_and_subs_for_query ( $query ) {
  351. try {
  352. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  353. $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`");
  354. $stmt->bindParam(':query', $quer);
  355. $quer = "%" . $query . "%";
  356. if ($stmt->execute()) {
  357. $result = $stmt->fetchAll();
  358. return $result;
  359. } else {
  360. return FALSE;
  361. }
  362. } catch (PDOException $e) {
  363. echo $e->getMessage() . "<br>";
  364. }
  365. }
  366. function ctv_get_apps_and_docs_for_query ( $query ) { // ***
  367. try {
  368. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  369. $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`");
  370. $stmt->bindParam(':query', $quer);
  371. $quer = "%" . $query . "%";
  372. if ($stmt->execute()) {
  373. $result = $stmt->fetchAll();
  374. return $result;
  375. } else {
  376. return FALSE;
  377. }
  378. } catch (PDOException $e) {
  379. echo $e->getMessage() . "<br>";
  380. }
  381. }
  382. function ctv_get_products_for_application ( $applno ) { // This function may no longer be needed
  383. try {
  384. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  385. $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`)");
  386. $stmt->bindParam(':applno', $an);
  387. $an = $applno;
  388. if ($stmt->execute()) {
  389. $result = $stmt->fetchAll();
  390. return $result;
  391. } else {
  392. return FALSE;
  393. }
  394. } catch (PDOException $e) {
  395. echo $e->getMessage() . "<br>";
  396. }
  397. }
  398. function ctv_get_documents_for_product ( $applno ) {
  399. try {
  400. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  401. $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`");
  402. $stmt->bindParam(':applno', $an);
  403. $an = $applno;
  404. if ($stmt->execute()) {
  405. $result = $stmt->fetchAll();
  406. return $result;
  407. } else {
  408. return FALSE;
  409. }
  410. } catch (PDOException $e) {
  411. echo $e->getMessage() . "<br>";
  412. }
  413. }
  414. function ctv_get_submissions_for_product ( $applno ) {
  415. try {
  416. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  417. $stmt = $dbh->prepare("SELECT * FROM `fda_Submissions`, `fda_SubmissionClass_Lookup` WHERE `ApplNo` = :applno AND (`fda_Submissions`.`SubmissionClassCodeID` = `fda_SubmissionClass_Lookup`.`SubmissionClassCodeID`)");
  418. $stmt->bindParam(':applno', $an);
  419. $an = $applno;
  420. if ($stmt->execute()) {
  421. $result = $stmt->fetchAll();
  422. return $result;
  423. } else {
  424. return FALSE;
  425. }
  426. } catch (PDOException $e) {
  427. echo $e->getMessage() . "<br>";
  428. }
  429. }
  430. function ctv_get_first_start_for_address ( $address, $ctviewer ) {
  431. // get the first start date
  432. try {
  433. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  434. $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");
  435. $stmt->bindParam(':address', $addy);
  436. $addy = $address;
  437. $stmt->execute();
  438. $result = $stmt->fetchAll();
  439. $dbh = null;
  440. $first_start = $result[0]['Start_Date'];
  441. $all_dates[] = $first_start;
  442. } catch (PDOException $e) {
  443. echo $e->getMessage();
  444. }
  445. // get first primary completion date
  446. try {
  447. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  448. $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");
  449. $stmt->bindParam(':address', $addy);
  450. $addy = $address;
  451. $stmt->execute();
  452. $result = $stmt->fetchAll();
  453. $dbh = null;
  454. $first_primary_completion = $result[0]['Primary_Completion_Date'];
  455. if ( count($result) > 0 && $first_primary_completion != "0000-00-00" ) {
  456. $all_dates[] = $first_primary_completion;
  457. }
  458. } catch (PDOException $e) {
  459. echo $e->getMessage();
  460. }
  461. // get first completion date
  462. try {
  463. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  464. $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");
  465. $stmt->bindParam(':address', $addy);
  466. $addy = $address;
  467. $stmt->execute();
  468. $result = $stmt->fetchAll();
  469. $dbh = null;
  470. $first_completion = $result[0]['Completion_Date'];
  471. if ( count($result) > 0 && $first_completion != "0000-00-00" ) {
  472. $all_dates[] = $first_completion;
  473. }
  474. } catch (PDOException $e) {
  475. echo $e->getMessage();
  476. }
  477. // get first date from fda submissions
  478. try {
  479. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  480. $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");
  481. $stmt->bindParam(':query', $quer);
  482. $quer = "%" . $ctviewer['query'] . "%";
  483. $stmt->execute();
  484. $result = $stmt->fetchAll();
  485. $dbh = null;
  486. $first_fda_submission = substr($result[0]['SubmissionStatusDate'], 0, 10);
  487. if ( count($result) > 0 && $first_fda_submission != "0000-00-00" ) {
  488. $all_dates[] = $first_fda_submission;
  489. }
  490. } catch (PDOException $e) {
  491. echo $e->getMessage();
  492. }
  493. // get first date from fda application docs
  494. try {
  495. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  496. $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");
  497. $stmt->bindParam(':query', $quer);
  498. $quer = "%" . $ctviewer['query'] . "%";
  499. $stmt->execute();
  500. $result = $stmt->fetchAll();
  501. $dbh = null;
  502. $first_fda_application_doc = substr($result[0]['ApplicationDocsDate'], 0, 10);
  503. if ( count($result) > 0 && $first_fda_application_doc != "0000-00-00" ) {
  504. $all_dates[] = $first_fda_application_doc;
  505. }
  506. } catch (PDOException $e) {
  507. echo $e->getMessage();
  508. }
  509. $first_date = min($all_dates);
  510. return substr($first_date, 0, 7) . "-01";
  511. }
  512. function ctv_get_last_end_for_address ( $address, $ctviewer ) {
  513. // get the first start date
  514. try {
  515. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  516. $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");
  517. $stmt->bindParam(':address', $addy);
  518. $addy = $address;
  519. $stmt->execute();
  520. $result = $stmt->fetchAll();
  521. $dbh = null;
  522. $last_start = $result[0]['Start_Date'];
  523. } catch (PDOException $e) {
  524. echo $e->getMessage();
  525. }
  526. // get first primary completion date
  527. try {
  528. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  529. $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");
  530. $stmt->bindParam(':address', $addy);
  531. $addy = $address;
  532. $stmt->execute();
  533. $result = $stmt->fetchAll();
  534. $dbh = null;
  535. $last_primary_completion = $result[0]['Primary_Completion_Date'];
  536. } catch (PDOException $e) {
  537. echo $e->getMessage();
  538. }
  539. // get first completion date
  540. try {
  541. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  542. $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");
  543. $stmt->bindParam(':address', $addy);
  544. $addy = $address;
  545. $stmt->execute();
  546. $result = $stmt->fetchAll();
  547. $dbh = null;
  548. $last_completion = $result[0]['Completion_Date'];
  549. } catch (PDOException $e) {
  550. echo $e->getMessage();
  551. }
  552. // get first date from fda submissions
  553. try {
  554. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  555. $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");
  556. $stmt->bindParam(':query', $quer);
  557. $quer = "%" . $ctviewer['query'] . "%";
  558. $stmt->execute();
  559. $result = $stmt->fetchAll();
  560. $dbh = null;
  561. $last_fda_submission = substr($result[0]['SubmissionStatusDate'], 0, 10);
  562. } catch (PDOException $e) {
  563. echo $e->getMessage();
  564. }
  565. // get first date from fda application docs
  566. try {
  567. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  568. $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");
  569. $stmt->bindParam(':query', $quer);
  570. $quer = "%" . $ctviewer['query'] . "%";
  571. $stmt->execute();
  572. $result = $stmt->fetchAll();
  573. $dbh = null;
  574. $last_fda_application_doc = substr($result[0]['ApplicationDocsDate'], 0, 10);
  575. } catch (PDOException $e) {
  576. echo $e->getMessage();
  577. }
  578. $last_date = max($last_start, $last_primary_completion, $last_completion, $last_fda_submission, $last_fda_application_doc);
  579. return substr($last_date, 0, 7) . "-01";
  580. }
  581. function ctv_get_width_of_display ($start, $end) {
  582. $function_name = "ctg_get_width_of_display";
  583. $startyear = substr ($start, 0, 4);
  584. $endyear = substr ($end, 0, 4);
  585. $difference = $endyear - $startyear + 1;
  586. $width = 120 * $difference;
  587. if ( $width < 940) {
  588. return 940;
  589. } else {
  590. return $width;
  591. }
  592. }
  593. function ctv_sort_by ( $sortcolumn, $address ) {
  594. // Supported values for $sortcolumn are:
  595. // Start_Date, End_Date, Funding, Phase
  596. try {
  597. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  598. $stmt = $dbh->prepare("UPDATE `ctviewers` SET `sort_by` = :sort WHERE `address` = :address LIMIT 1;");
  599. $stmt->bindParam(':sort', $sort);
  600. $stmt->bindParam(':address', $addy);
  601. $sort = $sortcolumn;
  602. $addy = $address;
  603. if ($stmt->execute()) {
  604. return TRUE;
  605. } else {
  606. return FALSE;
  607. }
  608. } catch (PDOException $e) {
  609. echo $e->getMessage() . "<br>";
  610. }
  611. }
  612. function ctv_get_pmc_for_nda ( $nda_number ) {
  613. try {
  614. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  615. $stmt = $dbh->prepare("SELECT * FROM `pmc_commitments`, `pmc_cmt_status` WHERE (`pmc_commitments`.`CMT_STATUS` = `pmc_cmt_status`.`CMT_Status`) AND (`NDA_NUMBER` = :nda) ");
  616. $stmt->bindParam(':nda', $nda);
  617. $nda = $nda_number;
  618. if ($stmt->execute()) {
  619. $result = $stmt->fetchAll();
  620. return $result;
  621. } else {
  622. return FALSE;
  623. }
  624. } catch (PDOException $e) {
  625. echo $e->getMessage() . "<br>";
  626. }
  627. }
  628. function ctv_get_apps_and_pmcs_for_query ( $query ) {
  629. try {
  630. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  631. $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`");
  632. $stmt->bindParam(':query', $quer);
  633. $quer = "%" . $query . "%";
  634. if ($stmt->execute()) {
  635. $result = $stmt->fetchAll();
  636. return $result;
  637. } else {
  638. return FALSE;
  639. }
  640. } catch (PDOException $e) {
  641. echo $e->getMessage() . "<br>";
  642. }
  643. }
  644. ?>