A web app for keeping track of media that passes or fails The Queerbait-Tragicqueer-Straightcomfort Test
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.
 
 
 

709 lines
16 KiB

  1. <?php
  2. function get_front_page_table () {
  3. try {
  4. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  5. $stmt = $dbh->prepare("SELECT * FROM `media` WHERE `approved` = 1 ORDER BY `title`, `year`, `details`;");
  6. $stmt->execute();
  7. $result = $stmt->fetchAll();
  8. return $result;
  9. }
  10. catch (PDOException $e) {
  11. echo $e->getMessage();
  12. }
  13. }
  14. function get_admin_page_table () {
  15. try {
  16. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  17. $stmt = $dbh->prepare("SELECT * FROM `media` ORDER BY `approved`, `title`, `year`, `details`;");
  18. $stmt->execute();
  19. $result = $stmt->fetchAll();
  20. return $result;
  21. }
  22. catch (PDOException $e) {
  23. echo $e->getMessage();
  24. }
  25. }
  26. function admin_save_new_entry ($type, $title, $year, $details, $imdb_link, $queer_character, $named, $name, $non_deniable, $blink_miss_it, $tragic, $straight_comfort, $notes, $notes_contain_spoilers) {
  27. try {
  28. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  29. $stmt = $dbh->prepare("INSERT INTO `media` (`type`, `title`, `year`, `details`, `imdb_link`, `queer_character`, `named`, `name`, `non_deniable`, `blink_miss_it`, `tragic`, `straight_comfort`, `notes`, `notes_contain_spoilers`, `submitter`, `approved`, `when_approved`) VALUES (:type, :title, :year, :details, :imdb_link, :queer_character, :named, :name, :non_deniable, :blink_miss_it, :tragic, :straight_comfort, :notes, :notes_contain_spoilers, 'bgcarlisle', 1, NOW())");
  30. $stmt->bindParam(':type', $ty);
  31. $stmt->bindParam(':title', $ti);
  32. $stmt->bindParam(':year', $ye);
  33. $stmt->bindParam(':details', $de);
  34. $stmt->bindParam(':imdb_link', $im);
  35. $stmt->bindParam(':queer_character', $qc);
  36. $stmt->bindParam(':named', $nd);
  37. $stmt->bindParam(':name', $na);
  38. $stmt->bindParam(':non_deniable', $no);
  39. $stmt->bindParam(':blink_miss_it', $bl);
  40. $stmt->bindParam(':tragic', $tr);
  41. $stmt->bindParam(':straight_comfort', $st);
  42. $stmt->bindParam(':notes', $nt);
  43. $stmt->bindParam(':notes_contain_spoilers', $ns);
  44. $ty = $type;
  45. $ti = $title;
  46. $ye = $year;
  47. if ( $details == "na" ) {
  48. $de = NULL;
  49. } else {
  50. $de = $details;
  51. }
  52. if ( $imdb_link == "" ) {
  53. $im = NULL;
  54. } else {
  55. $im = $imdb_link;
  56. }
  57. $qc = $queer_character;
  58. if ( $named == "na" ) {
  59. $nd = NULL;
  60. } else {
  61. $nd = $named;
  62. }
  63. if ( $name == "" ) {
  64. $na = NULL;
  65. } else {
  66. $na = $name;
  67. }
  68. if ( $non_deniable == "na" ) {
  69. $no = NULL;
  70. } else {
  71. $no = $non_deniable;
  72. }
  73. if ( $blink_miss_it == "na" ) {
  74. $bl = NULL;
  75. } else {
  76. $bl = $blink_miss_it;
  77. }
  78. if ( $tragic == "na" ) {
  79. $tr = NULL;
  80. } else {
  81. $tr = $tragic;
  82. }
  83. if ( $straight_comfort == "na" ) {
  84. $st = NULL;
  85. } else {
  86. $st = $straight_comfort;
  87. }
  88. if ( $notes == "" ) {
  89. $nt = NULL;
  90. } else {
  91. $nt = $notes;
  92. }
  93. if ( $notes_contain_spoilers == "na" ) {
  94. $ns = NULL;
  95. } else {
  96. $ns = $notes_contain_spoilers;
  97. }
  98. if ($stmt->execute()) {
  99. return TRUE;
  100. } else {
  101. return FALSE;
  102. }
  103. }
  104. catch (PDOException $e) {
  105. echo $e->getMessage();
  106. }
  107. }
  108. function admin_save_edit ($media_id, $type, $title, $year, $details, $imdb_link, $queer_character, $named, $name, $non_deniable, $blink_miss_it, $tragic, $straight_comfort, $notes, $notes_contain_spoilers) {
  109. try {
  110. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  111. $stmt = $dbh->prepare("UPDATE `media` SET `type`=:type, `title`=:title, `year`=:year, `details`=:details, `imdb_link`=:imdb_link, `queer_character`=:queer_character, `named`=:named, `name`=:name, `non_deniable`=:non_deniable, `blink_miss_it`=:blink_miss_it, `tragic`=:tragic, `straight_comfort`=:straight_comfort, `notes`=:notes, `notes_contain_spoilers`=:notes_contain_spoilers WHERE `id` = :mid");
  112. $stmt->bindParam(':type', $ty);
  113. $stmt->bindParam(':title', $ti);
  114. $stmt->bindParam(':year', $ye);
  115. $stmt->bindParam(':details', $de);
  116. $stmt->bindParam(':imdb_link', $im);
  117. $stmt->bindParam(':queer_character', $qc);
  118. $stmt->bindParam(':named', $nd);
  119. $stmt->bindParam(':name', $na);
  120. $stmt->bindParam(':non_deniable', $no);
  121. $stmt->bindParam(':blink_miss_it', $bl);
  122. $stmt->bindParam(':tragic', $tr);
  123. $stmt->bindParam(':straight_comfort', $st);
  124. $stmt->bindParam(':notes', $nt);
  125. $stmt->bindParam(':notes_contain_spoilers', $ns);
  126. $stmt->bindParam(':mid', $mid);
  127. $mid = $media_id;
  128. $ty = $type;
  129. $ti = $title;
  130. $ye = $year;
  131. if ( $details == "na" ) {
  132. $de = NULL;
  133. } else {
  134. $de = $details;
  135. }
  136. if ( $imdb_link == "" ) {
  137. $im = NULL;
  138. } else {
  139. $im = $imdb_link;
  140. }
  141. $qc = $queer_character;
  142. if ( $named == "na" ) {
  143. $nd = NULL;
  144. } else {
  145. $nd = $named;
  146. }
  147. if ( $name == "" ) {
  148. $na = NULL;
  149. } else {
  150. $na = $name;
  151. }
  152. if ( $non_deniable == "na" ) {
  153. $no = NULL;
  154. } else {
  155. $no = $non_deniable;
  156. }
  157. if ( $blink_miss_it == "na" ) {
  158. $bl = NULL;
  159. } else {
  160. $bl = $blink_miss_it;
  161. }
  162. if ( $tragic == "na" ) {
  163. $tr = NULL;
  164. } else {
  165. $tr = $tragic;
  166. }
  167. if ( $straight_comfort == "na" ) {
  168. $st = NULL;
  169. } else {
  170. $st = $straight_comfort;
  171. }
  172. if ( $notes == "" ) {
  173. $nt = NULL;
  174. } else {
  175. $nt = $notes;
  176. }
  177. if ( $notes_contain_spoilers == "na" ) {
  178. $ns = NULL;
  179. } else {
  180. $ns = $notes_contain_spoilers;
  181. }
  182. if ($stmt->execute()) {
  183. return TRUE;
  184. } else {
  185. return FALSE;
  186. }
  187. }
  188. catch (PDOException $e) {
  189. echo $e->getMessage();
  190. }
  191. }
  192. function submit_entry ($type, $title, $year, $details, $imdb_link, $queer_character, $named, $name, $non_deniable, $blink_miss_it, $tragic, $straight_comfort, $notes, $notes_contain_spoilers, $submitter) {
  193. try {
  194. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  195. $stmt = $dbh->prepare("INSERT INTO `media` (`type`, `title`, `year`, `details`, `imdb_link`, `queer_character`, `named`, `name`, `non_deniable`, `blink_miss_it`, `tragic`, `straight_comfort`, `notes`, `notes_contain_spoilers`, `submitter`, `approved`) VALUES (:type, :title, :year, :details, :imdb_link, :queer_character, :named, :name, :non_deniable, :blink_miss_it, :tragic, :straight_comfort, :notes, :notes_contain_spoilers, :submitter, 0)");
  196. $stmt->bindParam(':type', $ty);
  197. $stmt->bindParam(':title', $ti);
  198. $stmt->bindParam(':year', $ye);
  199. $stmt->bindParam(':details', $de);
  200. $stmt->bindParam(':imdb_link', $im);
  201. $stmt->bindParam(':queer_character', $qc);
  202. $stmt->bindParam(':named', $nd);
  203. $stmt->bindParam(':name', $na);
  204. $stmt->bindParam(':non_deniable', $no);
  205. $stmt->bindParam(':blink_miss_it', $bl);
  206. $stmt->bindParam(':tragic', $tr);
  207. $stmt->bindParam(':straight_comfort', $st);
  208. $stmt->bindParam(':notes', $nt);
  209. $stmt->bindParam(':notes_contain_spoilers', $ns);
  210. $stmt->bindParam(':submitter', $su);
  211. $ty = $type;
  212. $ti = $title;
  213. $ye = $year;
  214. if ( $details == "na" ) {
  215. $de = NULL;
  216. } else {
  217. $de = $details;
  218. }
  219. if ( $imdb_link == "" ) {
  220. $im = NULL;
  221. } else {
  222. $im = $imdb_link;
  223. }
  224. $qc = $queer_character;
  225. if ( $named == "na" ) {
  226. $nd = NULL;
  227. } else {
  228. $nd = $named;
  229. }
  230. if ( $name == "" ) {
  231. $na = NULL;
  232. } else {
  233. $na = $name;
  234. }
  235. if ( $non_deniable == "na" ) {
  236. $no = NULL;
  237. } else {
  238. $no = $non_deniable;
  239. }
  240. if ( $blink_miss_it == "na" ) {
  241. $bl = NULL;
  242. } else {
  243. $bl = $blink_miss_it;
  244. }
  245. if ( $tragic == "na" ) {
  246. $tr = NULL;
  247. } else {
  248. $tr = $tragic;
  249. }
  250. if ( $straight_comfort == "na" ) {
  251. $st = NULL;
  252. } else {
  253. $st = $straight_comfort;
  254. }
  255. if ( $notes == "" ) {
  256. $nt = NULL;
  257. } else {
  258. $nt = $notes;
  259. }
  260. if ( $notes_contain_spoilers == "na" ) {
  261. $ns = NULL;
  262. } else {
  263. $ns = $notes_contain_spoilers;
  264. }
  265. if ($submitter == "") {
  266. $su = NULL;
  267. } else {
  268. $su = $submitter;
  269. }
  270. if ($stmt->execute()) {
  271. return TRUE;
  272. } else {
  273. return FALSE;
  274. }
  275. }
  276. catch (PDOException $e) {
  277. echo $e->getMessage();
  278. }
  279. }
  280. function delete_media ( $media_id ) {
  281. try {
  282. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  283. $stmt = $dbh->prepare("DELETE FROM `media` WHERE `id` = :mid LIMIT 1;");
  284. $stmt->bindParam(':mid', $mid);
  285. $mid = $media_id;
  286. if ($stmt->execute()) {
  287. return TRUE;
  288. } else {
  289. return FALSE;
  290. }
  291. }
  292. catch (PDOException $e) {
  293. echo $e->getMessage();
  294. }
  295. }
  296. function get_single_entry ( $media_id ) {
  297. try {
  298. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  299. $stmt = $dbh->prepare("SELECT * FROM `media` WHERE `id` = :mid LIMIT 1;");
  300. $stmt->bindParam(':mid', $mid);
  301. $mid = $media_id;
  302. if ($stmt->execute()) {
  303. $result = $stmt->fetchAll();
  304. return $result[0];
  305. } else {
  306. return FALSE;
  307. }
  308. }
  309. catch (PDOException $e) {
  310. echo $e->getMessage();
  311. }
  312. }
  313. function update_approved ($media_id) {
  314. try {
  315. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  316. $stmt = $dbh->prepare("SELECT * FROM `media` WHERE `id` = :mid;");
  317. $stmt->bindParam(':mid', $mid);
  318. $mid = $media_id;
  319. $stmt->execute();
  320. $result = $stmt->fetchAll();
  321. $media = $result[0];
  322. }
  323. catch (PDOException $e) {
  324. echo $e->getMessage();
  325. }
  326. if ($media['approved'] === NULL) {
  327. $new_app = 1;
  328. } else {
  329. switch ($media['approved']) {
  330. case 0:
  331. $new_app = 1;
  332. break;
  333. case 1:
  334. $new_app = 0;
  335. break;
  336. }
  337. }
  338. // Removes submitter information for privacy reasons
  339. if ($media['submitter'] == "bgcarlisle") {
  340. $new_sub = "bgcarlisle";
  341. } else {
  342. $new_sub = NULL;
  343. }
  344. try {
  345. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  346. $stmt = $dbh->prepare("UPDATE `media` SET `approved` = :newapp, `submitter` = :newsub WHERE `id` = :mid;");
  347. $stmt->bindParam(':newapp', $na);
  348. $stmt->bindParam(':newsub', $ns);
  349. $stmt->bindParam(':mid', $mid);
  350. $mid = $media_id;
  351. $na = $new_app;
  352. $ns = $new_sub;
  353. if ($stmt->execute()) {
  354. if ($new_app === NULL) {
  355. $display_app = "-";
  356. } else {
  357. switch ($new_app) {
  358. case 0:
  359. $display_app = "No";
  360. break;
  361. case 1:
  362. $display_app = "Yes";
  363. break;
  364. }
  365. }
  366. try {
  367. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  368. $stmt = $dbh->prepare("UPDATE `media` SET `when_approved` = NOW() WHERE `id` = :mid;");
  369. $stmt->bindParam(':mid', $mid);
  370. $mid = $media_id;
  371. $stmt->execute();
  372. }
  373. catch (PDOException $e) {
  374. echo $e->getMessage();
  375. }
  376. return $display_app;
  377. } else {
  378. return "MySQL Error";
  379. }
  380. }
  381. catch (PDOException $e) {
  382. echo $e->getMessage();
  383. }
  384. }
  385. function make_backup () {
  386. exec('mysqldump --user=' . DB_USER . ' --password=' . DB_PASS . ' --host=' . DB_HOST . ' ' . DB_NAME . ' > ' . '/home/bgc_qt/backups/' . date('Y-m-d-H-i') . '.sql' );
  387. }
  388. function export_tsv () {
  389. exec ( "mysql -u " . DB_USER . " -p" . DB_PASS . " -h " . DB_HOST . " " . DB_NAME . " -B -e \"SELECT type, title, year, details, imdb_link, queer_character, named, name, non_deniable, blink_miss_it, tragic, straight_comfort, REPLACE(CONCAT('\\\"', notes, '\\\"'), CHAR(13,10), '') as notes, notes_contain_spoilers FROM media WHERE approved = 1 ORDER BY title, year, details;\" > " . ABS_PATH . "export.tsv" );
  390. }
  391. function suggest_correction ( $media_id, $suggestion, $submitter ) {
  392. try {
  393. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  394. $stmt = $dbh->prepare("INSERT INTO `corrections` (`media_id`, `suggestion`, `submitter`) VALUES (:mid, :sug, :sub)");
  395. $stmt->bindParam(':mid', $mid);
  396. $stmt->bindParam(':sug', $sug);
  397. $stmt->bindParam(':sub', $sub);
  398. if ( $media_id != "na" ) {
  399. $mid = $media_id;
  400. } else {
  401. $mid = NULL;
  402. }
  403. $sug = $suggestion;
  404. $sub = $submitter;
  405. if ($stmt->execute()) {
  406. if ( $media_id != "na" ) {
  407. $media = get_single_entry ($media_id);
  408. if ( $media['details'] != "" ) {
  409. $details = " " . $media['details'];
  410. } else {
  411. $details = "";
  412. }
  413. $subject = "Queer test: Suggested correction to " . $media['title'] . " (" . $media['year'] . ") " . $details;
  414. $body = "A suggested correction has been posted regarding " . $media['title'] . " (" . $media['year'] . ")" . $details . "\n\n";
  415. $body = $body . "DETAILS FOR ORIGINAL SUBMISSION\n\n";
  416. $body = $body . "Title: " . $media['title'] . "\n";
  417. $body = $body . "Year: " . $media['year'] . "\n";
  418. $body = $body . "Details: " . $media['details'] . "\n";
  419. $body = $body . "Link: " . $media['imdb_link'] . "\n";
  420. $body = $body . "Queer character: " . $media['queer_character'] . "\n";
  421. $body = $body . "Named: " . $media['named'] . "\n";
  422. $body = $body . "Name: " . $media['name'] . "\n";
  423. $body = $body . "Non-deniable: " . $media['non_deniable'] . "\n";
  424. $body = $body . "Blink-and-you-miss-it: " . $media['blink_miss_it'] . "\n";
  425. $body = $body . "Tragic: " . $media['tragic'] . "\n";
  426. $body = $body . "Written for straight comfort: " . $media['straight_comfort'] . "\n";
  427. $body = $body . "Notes: " . $media['notes'] . "\n";
  428. $body = $body . "Notes contain spoilers: " . $media['notes_contain_spoilers'] . "\n";
  429. $body = $body . "Original submitter: " . $media['submitter'] . "\n";
  430. $body = $body . "When approved: " . $media['when_approved'] . "\n\n";
  431. } else {
  432. $subject = "Queer test: Suggested correction";
  433. $body = "A suggestion correction has been posted with no piece of media selected.\n\n";
  434. }
  435. $body = $body . "DETAILS FOR SUGGESTED CORRECTION\n\n";
  436. $body = $body . "Suggested correction submitter: " . $submitter . "\n\nSuggestion: " . $suggestion;
  437. $headers = array (
  438. 'From' => ADMIN_EMAIL
  439. );
  440. mail(
  441. ADMIN_EMAIL,
  442. $subject,
  443. $body,
  444. $headers
  445. );
  446. return TRUE;
  447. } else {
  448. return FALSE;
  449. }
  450. }
  451. catch (PDOException $e) {
  452. echo $e->getMessage();
  453. }
  454. }
  455. function get_corrections () {
  456. try {
  457. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  458. $stmt = $dbh->prepare("SELECT * FROM `corrections` ORDER BY `when_submitted` DESC;");
  459. $stmt->execute();
  460. $result = $stmt->fetchAll();
  461. return $result;
  462. }
  463. catch (PDOException $e) {
  464. echo $e->getMessage();
  465. }
  466. }
  467. function delete_correction ($correction_id) {
  468. try {
  469. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  470. $stmt = $dbh->prepare("DELETE FROM `corrections` WHERE `id` = :cid LIMIT 1;");
  471. $stmt->bindParam(':cid', $cid);
  472. $cid = $correction_id;
  473. if ($stmt->execute()) {
  474. return TRUE;
  475. } else {
  476. return FALSE;
  477. }
  478. }
  479. catch (PDOException $e) {
  480. echo $e->getMessage();
  481. }
  482. }
  483. function delete_old_corrections () {
  484. try {
  485. $dbh = new PDO('mysql:dbname=' . DB_NAME . ';host=' . DB_HOST, DB_USER, DB_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
  486. $stmt = $dbh->prepare("DELETE FROM `corrections` WHERE `when_submitted` < NOW() - INTERVAL 3 DAY;");
  487. $stmt->bindParam(':cid', $cid);
  488. $cid = $correction_id;
  489. if ($stmt->execute()) {
  490. return TRUE;
  491. } else {
  492. return FALSE;
  493. }
  494. }
  495. catch (PDOException $e) {
  496. echo $e->getMessage();
  497. }
  498. }
  499. ?>