Planned downtime from 11:00 UTC to 11:30 UTC. We try to keep it as short as possilble!
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.
 
 
 
 

1153 lines
42 KiB

  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_update_dev_info () {
  188. // Delete the old medical devices file
  189. exec("rm -rdf " . ABS_PATH . "dl/dev/");
  190. // Download a fresh one!
  191. if ( file_put_contents( ABS_PATH . "dl/pma.zip", file_get_contents("http://www.accessdata.fda.gov/premarket/ftparea/pma.zip")) ) {
  192. echo "Downloaded medical devices PMA database<br>";
  193. mkdir (ABS_PATH . "dl/dev/");
  194. $zip = new ZipArchive;
  195. $res = $zip->open( ABS_PATH . "dl/pma.zip" );
  196. if ( $res === TRUE ) {
  197. $zip->extractTo( ABS_PATH . "dl/dev/" );
  198. $zip->close();
  199. echo "Extracted medical devices PMA database<br>";
  200. exec ("dos2unix " . ABS_PATH . "dl/dev/*");
  201. try {
  202. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  203. $stmt = $dbh->prepare("TRUNCATE TABLE devices_pma;");
  204. if ($stmt->execute()) {
  205. echo "Successfully cleared old medical devices data from db<br><br>";
  206. } else {
  207. echo "Error clearing old medical devices data<br><br>";
  208. }
  209. $dbh = null;
  210. } catch (PDOException $e) {
  211. echo $e->getMessage();
  212. }
  213. $pma_abs_file = ABS_PATH . "dl/dev/pma.txt";
  214. $filename = substr($pma_abs_file, strrpos ($fda_abs_file, "/")+1);
  215. $pma_file_handle = fopen($pma_abs_file, "r") or die("Unable to open " . $filename . "<br>");
  216. $table_columns = str_replace("|", ", ", fgets($pma_file_handle));
  217. // echo "Columns: " . $table_columns;
  218. fclose($pma_file_handle);
  219. try {
  220. $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));
  221. $stmt = $dbh->prepare(
  222. "LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `devices_pma` " .
  223. "FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES " .
  224. "(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) " .
  225. "SET `DATERECEIVED` = nullif(STR_TO_DATE(@DATERECEIVED, '%c/%e/%Y'), @DATERECEIVED), " .
  226. "`DECISIONDATE` = nullif(STR_TO_DATE(@DECISIONDATE, '%c/%e/%Y'), @DECISIONDATE), " .
  227. "`FEDREGNOTICEDATE` = nullif(STR_TO_DATE(@FEDREGNOTICEDATE, '%c/%e/%Y'), @FEDREGNOTICEDATE);"
  228. );
  229. $stmt->bindParam(':file', $file);
  230. $file = $pma_abs_file;
  231. if ( file_exists ($file) ) {
  232. echo "File " . $file . " exists<br>";
  233. } else {
  234. echo "File " . $file . " does not exist<br>";
  235. }
  236. if ($stmt->execute()) {
  237. echo "File " . $file . " has been added to the database<br>";
  238. } else {
  239. echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
  240. echo $stmt->errorCode() . "<br>";
  241. $errorInfo = $stmt->errorInfo();
  242. echo $errorInfo[1] . "<br>";
  243. echo $errorInfo[2] . "<br>";
  244. }
  245. $dbh = null;
  246. } catch (PDOException $e) {
  247. echo $e->getMessage();
  248. }
  249. } else {
  250. echo "Failure to extract medical devices PMA database<br>";
  251. }
  252. } else {
  253. echo "Failure to download medical devices PMA database<br>";
  254. }
  255. unlink(ABS_PATH . "dl/pma.zip");
  256. }
  257. function ctv_query_ct_dot_gov ( $query, $indication, $paeds ) {
  258. // Make a new address
  259. $haveuniqueaddress = 0;
  260. while ( $haveuniqueaddress == 0 ) {
  261. $address = md5(uniqid(rand(), true));
  262. try {
  263. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  264. $stmt = $dbh->prepare("SELECT `address` FROM `ctviewers` WHERE `address` = :address LIMIT 1");
  265. $stmt->bindParam(':address', $addy);
  266. $addy = $address;
  267. $stmt->execute();
  268. $result = $stmt->fetchAll();
  269. $dbh = null;
  270. if ( count ($result) == 0 ) {
  271. $haveuniqueaddress = 1;
  272. }
  273. }
  274. catch (PDOException $e) {
  275. echo $e->getMessage() . "<br>";
  276. }
  277. }
  278. $query = urlencode ($query);
  279. $indication = urlencode ($indication);
  280. if ( $_POST['indication'] == "" ) { // If no indication is entered
  281. $indication_url = "";
  282. } else {
  283. $indication_url = "cond=" . $indication . "&";
  284. }
  285. if ( $paeds == "false" ) { // Paeds box not checked
  286. $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";
  287. } else { // Paeds box checked
  288. $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";
  289. }
  290. if ( file_put_contents( ABS_PATH . "dl/" . $address . ".tsv", file_get_contents($ct_dot_gov_url)) ) {
  291. // convert to unix
  292. exec ("dos2unix " . ABS_PATH . "dl/" . $address . ".tsv");
  293. // make a new viewer in the ctviewers table
  294. try {
  295. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  296. if ( $indication == "" ) {
  297. $stmt = $dbh->prepare("INSERT INTO `ctviewers` (`address`, `query`, `paeds`) VALUES (:address, :query, :paeds);");
  298. } else {
  299. $stmt = $dbh->prepare("INSERT INTO `ctviewers` (`address`, `query`, `indication`, `paeds`) VALUES (:address, :query, :indication, :paeds);");
  300. }
  301. $stmt->bindParam(':address', $addy);
  302. $stmt->bindParam(':query', $quer);
  303. if ( $indication != "" ) {
  304. $stmt->bindParam(':indication', $indi);
  305. }
  306. $stmt->bindParam(':paeds', $pae);
  307. $quer = $query;
  308. $indi = $indication;
  309. $addy = $address;
  310. if ( $paeds == "true" ) {
  311. $pae = 1;
  312. } else {
  313. $pae = 0;
  314. }
  315. if (! $stmt->execute()) {
  316. echo "Unable to insert new viewer into database<br>";
  317. }
  318. } catch (PDOException $e) {
  319. echo $e->getMessage() . "<br>";
  320. }
  321. // insert trial data into ctg_trials table
  322. try {
  323. $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));
  324. $stmt = $dbh->prepare(
  325. "LOAD DATA LOCAL INFILE :file REPLACE INTO TABLE `ctg_trials` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES " .
  326. "(`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`) " .
  327. "SET `address` = :address, " .
  328. "`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, " .
  329. "`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, " .
  330. "`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, " .
  331. "`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, " .
  332. "`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, " .
  333. "`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"
  334. );
  335. $stmt->bindParam(':file', $file);
  336. $stmt->bindParam(':address', $addy);
  337. $addy = $address;
  338. $file = ABS_PATH . "dl/" . $address . ".tsv";
  339. if ($stmt->execute() ) {
  340. echo $address;
  341. } else {
  342. echo "File " . $file . " has not been added to the db due to a MySQL error with code ";
  343. echo $stmt->errorCode() . "<br>";
  344. $errorInfo = $stmt->errorInfo();
  345. echo $errorInfo[1] . "<br>";
  346. echo $errorInfo[2] . "<br>";
  347. }
  348. } catch (PDOException $e) {
  349. echo $e->getMessage() . "<br>";
  350. }
  351. unlink (ABS_PATH . "dl/" . $address . ".tsv");
  352. } else {
  353. echo "FALSE";
  354. }
  355. }
  356. function ctv_get_ctviewer_for_address ( $address ) {
  357. try {
  358. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  359. $stmt = $dbh->prepare("SELECT * FROM `ctviewers` WHERE `address` = :address");
  360. $stmt->bindParam(':address', $addy);
  361. $addy = $address;
  362. if ($stmt->execute()) {
  363. $result = $stmt->fetchAll();
  364. return $result[0];
  365. } else {
  366. return FALSE;
  367. }
  368. } catch (PDOException $e) {
  369. echo $e->getMessage() . "<br>";
  370. }
  371. }
  372. function ctv_get_trials_for_address ( $address ) {
  373. try {
  374. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  375. $stmt = $dbh->prepare("SELECT *, COALESCE(`Primary_Completion_Date`, `Completion_Date`) as `End_Date` FROM `ctg_trials` WHERE `address` = :address ORDER BY `Start_Date`;");
  376. $stmt->bindParam(':address', $addy);
  377. $addy = $address;
  378. if ($stmt->execute()) {
  379. $result = $stmt->fetchAll();
  380. return $result;
  381. } else {
  382. return FALSE;
  383. }
  384. } catch (PDOException $e) {
  385. echo $e->getMessage() . "<br>";
  386. }
  387. }
  388. function ctv_get_applications_for_query ( $query ) {
  389. try {
  390. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  391. $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`");
  392. $stmt->bindParam(':query', $quer);
  393. $quer = "%" . $query . "%";
  394. if ($stmt->execute()) {
  395. $result = $stmt->fetchAll();
  396. return $result;
  397. } else {
  398. return FALSE;
  399. }
  400. } catch (PDOException $e) {
  401. echo $e->getMessage() . "<br>";
  402. }
  403. }
  404. function ctv_get_devices_for_query ( $query ) {
  405. $query = urldecode ($query);
  406. try {
  407. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  408. $stmt = $dbh->prepare("SELECT * FROM `devices_pma` WHERE `GENERICNAME` LIKE :query OR `TRADENAME` LIKE :query ORDER BY `DECISIONDATE`;");
  409. $stmt->bindParam(':query', $quer);
  410. $quer = "%" . urldecode ($query) . "%";
  411. if ($stmt->execute()) {
  412. $result = $stmt->fetchAll();
  413. return $result;
  414. } else {
  415. return FALSE;
  416. }
  417. } catch (PDOException $e) {
  418. echo $e->getMessage() . "<br>";
  419. }
  420. }
  421. function ctv_get_apps_and_products_for_query ( $query ) {
  422. try {
  423. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  424. $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`)");
  425. $stmt->bindParam(':query', $quer);
  426. $quer = "%" . $query . "%";
  427. if ($stmt->execute()) {
  428. $result = $stmt->fetchAll();
  429. return $result;
  430. } else {
  431. return FALSE;
  432. }
  433. } catch (PDOException $e) {
  434. echo $e->getMessage() . "<br>";
  435. }
  436. }
  437. function ctv_get_apps_and_subs_for_query ( $query ) {
  438. try {
  439. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  440. $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`");
  441. $stmt->bindParam(':query', $quer);
  442. $quer = "%" . $query . "%";
  443. if ($stmt->execute()) {
  444. $result = $stmt->fetchAll();
  445. return $result;
  446. } else {
  447. return FALSE;
  448. }
  449. } catch (PDOException $e) {
  450. echo $e->getMessage() . "<br>";
  451. }
  452. }
  453. function ctv_get_apps_and_docs_for_query ( $query ) { // ***
  454. try {
  455. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  456. $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`");
  457. $stmt->bindParam(':query', $quer);
  458. $quer = "%" . $query . "%";
  459. if ($stmt->execute()) {
  460. $result = $stmt->fetchAll();
  461. return $result;
  462. } else {
  463. return FALSE;
  464. }
  465. } catch (PDOException $e) {
  466. echo $e->getMessage() . "<br>";
  467. }
  468. }
  469. function ctv_get_products_for_application ( $applno ) { // This function may no longer be needed
  470. try {
  471. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  472. $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`)");
  473. $stmt->bindParam(':applno', $an);
  474. $an = $applno;
  475. if ($stmt->execute()) {
  476. $result = $stmt->fetchAll();
  477. return $result;
  478. } else {
  479. return FALSE;
  480. }
  481. } catch (PDOException $e) {
  482. echo $e->getMessage() . "<br>";
  483. }
  484. }
  485. function ctv_get_documents_for_product ( $applno ) {
  486. try {
  487. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  488. $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`");
  489. $stmt->bindParam(':applno', $an);
  490. $an = $applno;
  491. if ($stmt->execute()) {
  492. $result = $stmt->fetchAll();
  493. return $result;
  494. } else {
  495. return FALSE;
  496. }
  497. } catch (PDOException $e) {
  498. echo $e->getMessage() . "<br>";
  499. }
  500. }
  501. function ctv_get_submissions_for_product ( $applno ) {
  502. try {
  503. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  504. $stmt = $dbh->prepare("SELECT * FROM `fda_Submissions`, `fda_SubmissionClass_Lookup` WHERE `ApplNo` = :applno AND (`fda_Submissions`.`SubmissionClassCodeID` = `fda_SubmissionClass_Lookup`.`SubmissionClassCodeID`)");
  505. $stmt->bindParam(':applno', $an);
  506. $an = $applno;
  507. if ($stmt->execute()) {
  508. $result = $stmt->fetchAll();
  509. return $result;
  510. } else {
  511. return FALSE;
  512. }
  513. } catch (PDOException $e) {
  514. echo $e->getMessage() . "<br>";
  515. }
  516. }
  517. function ctv_get_first_start_for_address ( $address, $ctviewer ) {
  518. // get the first start date
  519. try {
  520. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  521. $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");
  522. $stmt->bindParam(':address', $addy);
  523. $addy = $address;
  524. $stmt->execute();
  525. $result = $stmt->fetchAll();
  526. $dbh = null;
  527. $first_start = $result[0]['Start_Date'];
  528. $all_dates[] = $first_start;
  529. } catch (PDOException $e) {
  530. echo $e->getMessage();
  531. }
  532. // get first primary completion date
  533. try {
  534. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  535. $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");
  536. $stmt->bindParam(':address', $addy);
  537. $addy = $address;
  538. $stmt->execute();
  539. $result = $stmt->fetchAll();
  540. $dbh = null;
  541. $first_primary_completion = $result[0]['Primary_Completion_Date'];
  542. if ( count($result) > 0 && $first_primary_completion != "0000-00-00" ) {
  543. $all_dates[] = $first_primary_completion;
  544. }
  545. } catch (PDOException $e) {
  546. echo $e->getMessage();
  547. }
  548. // get first completion date
  549. try {
  550. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  551. $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");
  552. $stmt->bindParam(':address', $addy);
  553. $addy = $address;
  554. $stmt->execute();
  555. $result = $stmt->fetchAll();
  556. $dbh = null;
  557. $first_completion = $result[0]['Completion_Date'];
  558. if ( count($result) > 0 && $first_completion != "0000-00-00" ) {
  559. $all_dates[] = $first_completion;
  560. }
  561. } catch (PDOException $e) {
  562. echo $e->getMessage();
  563. }
  564. // get first date from fda submissions
  565. try {
  566. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  567. $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");
  568. $stmt->bindParam(':query', $quer);
  569. $quer = "%" . $ctviewer['query'] . "%";
  570. $stmt->execute();
  571. $result = $stmt->fetchAll();
  572. $dbh = null;
  573. $first_fda_submission = substr($result[0]['SubmissionStatusDate'], 0, 10);
  574. if ( count($result) > 0 && $first_fda_submission != "0000-00-00" ) {
  575. $all_dates[] = $first_fda_submission;
  576. }
  577. } catch (PDOException $e) {
  578. echo $e->getMessage();
  579. }
  580. // get first date from fda application docs
  581. try {
  582. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  583. $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");
  584. $stmt->bindParam(':query', $quer);
  585. $quer = "%" . $ctviewer['query'] . "%";
  586. $stmt->execute();
  587. $result = $stmt->fetchAll();
  588. $dbh = null;
  589. $first_fda_application_doc = substr($result[0]['ApplicationDocsDate'], 0, 10);
  590. if ( count($result) > 0 && $first_fda_application_doc != "0000-00-00" ) {
  591. $all_dates[] = $first_fda_application_doc;
  592. }
  593. } catch (PDOException $e) {
  594. echo $e->getMessage();
  595. }
  596. // get date from medical device PMA's
  597. try {
  598. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  599. $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");
  600. $stmt->bindParam(':query', $quer);
  601. $quer = "%" . urldecode($ctviewer['query']) . "%";
  602. $stmt->execute();
  603. $result = $stmt->fetchAll();
  604. $dbh = null;
  605. $first_device_pma = substr($result[0]['DECISIONDATE'], 0, 10);
  606. if ( count($result) > 0 && $first_device_pma != "0000-00-00" ) {
  607. $all_dates[] = $first_device_pma;
  608. }
  609. } catch (PDOException $e) {
  610. echo $e->getMessage();
  611. }
  612. $first_date = min($all_dates);
  613. return substr($first_date, 0, 7) . "-01";
  614. }
  615. function ctv_get_last_end_for_address ( $address, $ctviewer ) {
  616. // get the first start date
  617. try {
  618. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  619. $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");
  620. $stmt->bindParam(':address', $addy);
  621. $addy = $address;
  622. $stmt->execute();
  623. $result = $stmt->fetchAll();
  624. $dbh = null;
  625. $last_start = $result[0]['Start_Date'];
  626. } catch (PDOException $e) {
  627. echo $e->getMessage();
  628. }
  629. // get first primary completion date
  630. try {
  631. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  632. $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");
  633. $stmt->bindParam(':address', $addy);
  634. $addy = $address;
  635. $stmt->execute();
  636. $result = $stmt->fetchAll();
  637. $dbh = null;
  638. $last_primary_completion = $result[0]['Primary_Completion_Date'];
  639. } catch (PDOException $e) {
  640. echo $e->getMessage();
  641. }
  642. // get first completion date
  643. try {
  644. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  645. $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");
  646. $stmt->bindParam(':address', $addy);
  647. $addy = $address;
  648. $stmt->execute();
  649. $result = $stmt->fetchAll();
  650. $dbh = null;
  651. $last_completion = $result[0]['Completion_Date'];
  652. } catch (PDOException $e) {
  653. echo $e->getMessage();
  654. }
  655. // get first date from fda submissions
  656. try {
  657. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  658. $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");
  659. $stmt->bindParam(':query', $quer);
  660. $quer = "%" . $ctviewer['query'] . "%";
  661. $stmt->execute();
  662. $result = $stmt->fetchAll();
  663. $dbh = null;
  664. $last_fda_submission = substr($result[0]['SubmissionStatusDate'], 0, 10);
  665. } catch (PDOException $e) {
  666. echo $e->getMessage();
  667. }
  668. // get first date from fda application docs
  669. try {
  670. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  671. $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");
  672. $stmt->bindParam(':query', $quer);
  673. $quer = "%" . $ctviewer['query'] . "%";
  674. $stmt->execute();
  675. $result = $stmt->fetchAll();
  676. $dbh = null;
  677. $last_fda_application_doc = substr($result[0]['ApplicationDocsDate'], 0, 10);
  678. } catch (PDOException $e) {
  679. echo $e->getMessage();
  680. }
  681. // get date from medical device PMA's
  682. try {
  683. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  684. $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");
  685. $stmt->bindParam(':query', $quer);
  686. $quer = "%" . $ctviewer['query'] . "%";
  687. $stmt->execute();
  688. $result = $stmt->fetchAll();
  689. $dbh = null;
  690. $first_device_pma = substr($result[0]['DECISIONDATE'], 0, 10);
  691. } catch (PDOException $e) {
  692. echo $e->getMessage();
  693. }
  694. $last_date = max($last_start, $last_primary_completion, $last_completion, $last_fda_submission, $last_fda_application_doc, $first_device_pma);
  695. return substr($last_date, 0, 7) . "-01";
  696. }
  697. function ctv_get_width_of_display ($start, $end) {
  698. $function_name = "ctg_get_width_of_display";
  699. $startyear = substr ($start, 0, 4);
  700. $endyear = substr ($end, 0, 4);
  701. $difference = $endyear - $startyear + 1;
  702. $width = 120 * $difference;
  703. if ( $width < 940) {
  704. return 940;
  705. } else {
  706. return $width;
  707. }
  708. }
  709. function ctv_sort_by ( $sortcolumn, $address ) {
  710. // Supported values for $sortcolumn are:
  711. // Start_Date, End_Date, Funding, Phase
  712. try {
  713. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  714. $stmt = $dbh->prepare("UPDATE `ctviewers` SET `sort_by` = :sort WHERE `address` = :address LIMIT 1;");
  715. $stmt->bindParam(':sort', $sort);
  716. $stmt->bindParam(':address', $addy);
  717. $sort = $sortcolumn;
  718. $addy = $address;
  719. if ($stmt->execute()) {
  720. return TRUE;
  721. } else {
  722. return FALSE;
  723. }
  724. } catch (PDOException $e) {
  725. echo $e->getMessage() . "<br>";
  726. }
  727. }
  728. function ctv_get_pmc_for_nda ( $nda_number ) {
  729. try {
  730. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  731. $stmt = $dbh->prepare("SELECT * FROM `pmc_commitments`, `pmc_cmt_status` WHERE (`pmc_commitments`.`CMT_STATUS` = `pmc_cmt_status`.`CMT_Status`) AND (`NDA_NUMBER` = :nda) ");
  732. $stmt->bindParam(':nda', $nda);
  733. $nda = $nda_number;
  734. if ($stmt->execute()) {
  735. $result = $stmt->fetchAll();
  736. return $result;
  737. } else {
  738. return FALSE;
  739. }
  740. } catch (PDOException $e) {
  741. echo $e->getMessage() . "<br>";
  742. }
  743. }
  744. function ctv_get_apps_and_pmcs_for_query ( $query ) {
  745. try {
  746. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  747. $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`");
  748. $stmt->bindParam(':query', $quer);
  749. $quer = "%" . $query . "%";
  750. if ($stmt->execute()) {
  751. $result = $stmt->fetchAll();
  752. return $result;
  753. } else {
  754. return FALSE;
  755. }
  756. } catch (PDOException $e) {
  757. echo $e->getMessage() . "<br>";
  758. }
  759. }
  760. ?>