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. ?>