_DatabaseHandler.php

Classes

NameDescription
_DatabaseHandler

File

classes/_DatabaseHandler.php
View source
  1. <?php
  2. class _DatabaseHandler
  3. {
  4. //
  5. public $dbc;
  6. function __construct()
  7. {
  8. $db_host = $GLOBALS["fp_system_settings"]["db_host"];
  9. $db_user = $GLOBALS["fp_system_settings"]["db_user"];
  10. $db_pass = $GLOBALS["fp_system_settings"]["db_pass"];
  11. $db_name = $GLOBALS["fp_system_settings"]["db_name"];
  12. $this->dbc = mysql_connect ($db_host, $db_user, $db_pass) or die('Could not connect to database: ' . mysql_error());
  13. mysql_select_db ($db_name);
  14. }
  15. function get_help_page($i)
  16. {
  17. $rtn_array = array();
  18. $res = $this->db_query("SELECT * FROM help WHERE `id`='?' ", $i);
  19. $cur = $this->db_fetch_array($res);
  20. $rtn_array["title"] = trim($cur["title"]);
  21. $rtn_array["body"] = trim($cur["body"]);
  22. return $rtn_array;
  23. }
  24. function add_to_log($action, $extra_data = "", $notes = "")
  25. {
  26. depricated_message();
  27. // Add a row to the log table.
  28. $ip = $_SERVER["REMOTE_ADDR"];
  29. $url = mysql_real_escape_string($_SERVER["REQUEST_URI"]);
  30. $user_id = $_SESSION["fp_user_id"];
  31. $user_type = $_SESSION["fp_user_type"];
  32. $user_name = mysql_real_escape_string($_SESSION["fp_user_name"]);
  33. $action = mysql_real_escape_string($action);
  34. $extra_data = mysql_real_escape_string($extra_data);
  35. $notes = mysql_real_escape_string($notes);
  36. if ($GLOBALS["fp_page_is_mobile"]) {
  37. $notes = "M:" . $notes;
  38. }
  39. // This needs to be mysql_query, instead of "this->db_query", because
  40. // otherwise it might get into an infinite loop.
  41. $now = time();
  42. $query = "INSERT INTO log (user_id,
  43. user_name, user_type, action, extra_data, notes,
  44. ip, posted, from_url) VALUES (
  45. '$user_id','$user_name','$user_type','$action','$extra_data',
  46. '$notes',
  47. '$ip', '$now' ,'$url') ";
  48. $res = mysql_query($query) or die(mysql_error() . " - " . $query);
  49. }
  50. /**
  51. * Sets the maintenance mode. $val should be either 0 (off) or 1 (on)
  52. *
  53. * @param integer $val
  54. */
  55. function set_maintenance_mode($val)
  56. {
  57. // Convenience function for setting maintenance mode. 0 = off, 1 = on.
  58. $this->set_settings_variable("maintenance_mode", $val);
  59. }
  60. function get_substitution_details($sub_id)
  61. {
  62. // Simply returns an associative array containing
  63. // the details of a substitution. The subID specified
  64. // is the actual id of the row of the database in
  65. // flightpath.student_substitutions.
  66. $rtn_array = array();
  67. $res = $this->db_query("SELECT * FROM student_substitutions
  68. WHERE id = '?' ", $sub_id);
  69. if ($this->db_num_rows($res) > 0)
  70. {
  71. $cur = $this->db_fetch_array($res);
  72. $rtn_array["faculty_id"] = $cur["faculty_id"];
  73. $rtn_array["remarks"] = trim($cur["sub_remarks"]);
  74. $rtn_array["required_course_id"] = $cur["required_course_id"];
  75. $rtn_array["required_group_id"] = $cur["required_group_id"];
  76. $rtn_array["posted"] = $cur["posted"];
  77. }
  78. return $rtn_array;
  79. }
  80. function update_user_settings_from_post($user_id)
  81. {
  82. // This will retrieve various user settings from the POST
  83. // and write them to the user_settings table as XML.
  84. $db = new DatabaseHandler();
  85. if ($user_id*1 < 1)
  86. {
  87. return false;
  88. }
  89. // First, we need to GET the user's settings array...
  90. if (!$user_settings_array = $this->get_user_settings($user_id))
  91. {
  92. // No existing userSettingsArray, or it's corrupted.
  93. // Make a new one.
  94. $user_settings_array = array();
  95. }
  96. // Now, update values in the settingsArray, if they are
  97. // present in the POST.
  98. if (trim($_POST["hide_charts"]) != "")
  99. {
  100. $user_settings_array["hide_charts"] = trim($_POST["hide_charts"]);
  101. }
  102. // Now, write it back to the settings table...
  103. $res = $this->db_query("REPLACE INTO user_settings(user_id,
  104. settings, posted)
  105. VALUES ('?','?', '?' )", $user_id, serialize($user_settings_array), time());
  106. watchdog("update_user_settings", "Hide charts set to: @hide", array("@hide" => $user_settings_array["hide_charts"]));
  107. return true;
  108. }
  109. function get_user_settings($user_id)
  110. {
  111. // return an array of this user's current settings.
  112. $res = $this->db_query("SELECT * FROM user_settings
  113. WHERE
  114. user_id = '?' ", $user_id);
  115. $cur = $this->db_fetch_array($res);
  116. if (!$rtn = unserialize($cur["settings"])) {
  117. $rtn = array();
  118. }
  119. return $rtn;
  120. }
  121. function get_developmental_requirements($student_cwid)
  122. {
  123. // returns an array which states whether or not the student
  124. // requires any developmental requirements.
  125. $rtn_array = array();
  126. $res = $this->db_query("SELECT * FROM student_developmentals
  127. WHERE student_id = '?' ", $student_cwid);
  128. while($cur = $this->db_fetch_array($res)) {
  129. $rtn_array[] = $cur["requirement"];
  130. }
  131. return $rtn_array;
  132. }
  133. function get_table_transfer_data_string($table_name, $table_structure, $where_clause = "")
  134. {
  135. // This function will return a string of all the data
  136. // in a particular table, formatted with delimeters.
  137. // %R~ separates rows, %C~ separates columns.
  138. // We expect the tableStructure to be a csv of the
  139. // column names.
  140. $rtn = "";
  141. $res = mysql_query("select $table_structure from $table_name $where_clause") or die_and_mail(mysql_error());
  142. while ($cur = mysql_fetch_row($res))
  143. {
  144. $new_row = "";
  145. foreach($cur as $key => $value)
  146. { // put all the values returned together...
  147. $new_row .= $value . "%C~";
  148. }
  149. // Remove last %C%...
  150. $new_row = substr($new_row, 0, -3);
  151. // Add it to the rtn...
  152. $rtn .= $new_row . "%R~";
  153. }
  154. // Remove the last %R%...
  155. $rtn = substr($rtn, 0, -3);
  156. return $rtn;
  157. }
  158. /**
  159. * This is a simple helper function which "escapes" the question marks (?) in
  160. * the string, by changing them to "??". This makes it suitable for use
  161. * within db_query(), but not necessary if used as an argument. Ex:
  162. * db_query("INSERT ... '" . $db->escape_question_marks($xml) . "' "); is good.
  163. * db_query("INSERT ... '?' ", $xml); is good. This function not needed.
  164. *
  165. * @param unknown_type $str
  166. */
  167. function escape_question_marks($str) {
  168. $rtn = str_replace("?", "??", $str);
  169. return $rtn;
  170. }
  171. /**
  172. * This function is used to perform a database query. It can take simple replacement patterns,
  173. * by using ?. If you actually need to have a ? in the query, you can escape it with ??.
  174. * For example:
  175. * $result = $db->db_query("SELECT * FROM table WHERE name = '?' and age = ? ", $name, $temp_age);
  176. *
  177. * @param unknown_type $sql_query
  178. * @return unknown
  179. */
  180. function db_query($sql_query) {
  181. // If there were any arguments to this function, then we must first apply
  182. // replacement patterns.
  183. $args = func_get_args();
  184. array_shift($args);
  185. if (is_array($args[0])) {
  186. // If the first argument was an array, it means we passed an array of values instead
  187. // of passing them directly. So use them directly as our args.
  188. $args = $args[0];
  189. }
  190. // The query may contain an escaped ?, meaning "??", so I will replace that with something
  191. // else first, then change it back afterwards.
  192. $sql_query = str_replace("??", "~ESCAPED_Q_MARK~", $sql_query);
  193. // If $c (number of replacements performed) does not match the number of replacements
  194. // specified, warn the user.
  195. if (substr_count($sql_query, "?") != count($args)) {
  196. fpm("<br><b>WARNING:</b> Replacement count does not match what was supplied to query: $sql_query<br><br>");
  197. }
  198. if (count($args) > 0) {
  199. // Replace each occurance of "?" with what's in our array.
  200. foreach ($args as $replacement) {
  201. // Escape the replacement...
  202. // The replacement might ALSO have a question mark in it. Escape that too.
  203. if (strpos($replacement, "?") !== 0) {
  204. $replacement = str_replace("?", "~ESCAPED_Q_MARK~", $replacement);
  205. }
  206. // Because mysql_real_escape_string will allow \' to pass through, I am going to
  207. // first use mysql_real_escape_string on all slashes.
  208. $replacement = str_replace("\\" , mysql_real_escape_string("\\"), $replacement);
  209. // Okay, perform the replacement
  210. $replacement = mysql_real_escape_string($replacement);
  211. // If we have a $ followed by a number (like $99), preg_replace will remove it. So, let's escape the $ if so.
  212. /// if so.
  213. $replacement = addcslashes($replacement, '$');
  214. $sql_query = preg_replace("/\?/", $replacement, $sql_query, 1);
  215. }
  216. }
  217. $sql_query = str_replace("~ESCAPED_Q_MARK~", "?", $sql_query);
  218. //////////////////////////////////////////////
  219. // Run the sqlQuery and return the result set.
  220. $result = mysql_query($sql_query, $this->dbc);
  221. if ($result)
  222. {
  223. return $result;
  224. } else {
  225. // Meaning, the query failed...
  226. // Do nothing. Do not attempt to log anything, as that could cause an infinite loop.
  227. // Display the error on screen
  228. $this->db_error();
  229. }
  230. }
  231. /**
  232. * Draw out the error onto the screen.
  233. *
  234. * @param unknown_type $sql
  235. */
  236. function db_error($msg = "")
  237. {
  238. $arr = debug_backtrace();
  239. $when_ts = time();
  240. $when_english = format_date($when_ts);
  241. $mysql_err = mysql_error();
  242. // If we are on production, email someone!
  243. if ($GLOBALS["fp_system_settings"]["notify_mysql_error_email_address"] != "")
  244. {
  245. $server = $_SERVER["SERVER_NAME"];
  246. $email_msg = t("A MYSQL error has occured in FlightPath.") . "
  247. Server: $server
  248. Timestamp: $when_ts ($when_english)
  249. Error:
  250. $mysql_err
  251. Comments:
  252. $msg
  253. Backtrace:
  254. " . print_r($arr, true) . "
  255. ";
  256. mail($GLOBALS["fp_system_settings"]["notify_mysql_error_email_address"], "FlightPath MYSQL Error Reported on $server", $email_msg);
  257. }
  258. fpm(t("A MySQL error has occured:") . " $mysql_err<br><br>" . t("The backtrace:"));
  259. fpm($arr);
  260. if ($GLOBALS["fp_die_mysql_errors"] == TRUE) {
  261. print "\n<br>The script has stopped executing because of a MySQL error:
  262. $mysql_err<br>\n
  263. Please fix the error and try again.<br>\n";
  264. print "<br><br>Timestamp: $when_ts ($when_english)
  265. <br><br>Program backtrace:
  266. <pre>" . print_r($arr, true) . "</pre>";
  267. die;
  268. }
  269. // Also, check to see if the mysql_err is because of a lost connection, as in, the
  270. // server went down. In that case, we should also terminate immediately, rather
  271. // than risk spamming an email recipient with error emails.
  272. if (stristr($mysql_err, "Lost connection to MySQL server")
  273. || stristr($mysql_err, "MySQL server has gone away")) {
  274. print "<h2 style='font-family: Arial, sans serif;'>Database Connection Error</h2>
  275. <br>
  276. <div style='font-size: 1.2em; font-family: Arial, sans serif; padding-left: 30px;
  277. padding-right: 30px;'>
  278. Sorry, but it appears the database is currently unavailable. This may
  279. simply be part of scheduled maintenance to the database server. Please
  280. try again in a few minutes. If the problem persists for longer
  281. than an hour, contact your technical support
  282. staff.
  283. </div>
  284. ";
  285. die;
  286. }
  287. }
  288. function request_new_group_id()
  289. {
  290. // Return a valid new group_id...
  291. for ($t = 0; $t < 100; $t++)
  292. {
  293. $id = mt_rand(1,9999999);
  294. // Check for collisions...
  295. $res4 = $this->db_query("SELECT * FROM draft_group_requirements
  296. WHERE group_id = '$id' LIMIT 1");
  297. if ($this->db_num_rows($res4) == 0)
  298. { // Was not in the table already, so use it!
  299. return $id;
  300. }
  301. }
  302. return false;
  303. }
  304. function request_new_course_id()
  305. {
  306. // Return a valid new course_id...
  307. for ($t = 0; $t < 100; $t++)
  308. {
  309. $id = mt_rand(1,9999999);
  310. // Check for collisions...
  311. $res4 = $this->db_query("SELECT * FROM draft_courses
  312. WHERE course_id = '$id' LIMIT 1");
  313. if ($this->db_num_rows($res4) == 0)
  314. { // Was not in the table already, so use it!
  315. return $id;
  316. }
  317. }
  318. return false;
  319. }
  320. function load_course_descriptive_data($course = null, $course_id = 0)
  321. {
  322. $current_catalog_year = variable_get("current_catalog_year", "2006");
  323. $catalog_year = $current_catalog_year; // currentCatalogYear.
  324. if ($course != null)
  325. {
  326. $course_id = $course->course_id;
  327. $catalog_year = $course->catalog_year;
  328. }
  329. $cache_catalog_year = $catalog_year;
  330. $cache_catalog_year = 0;
  331. $array_valid_names = array();
  332. // First-- is this course in our GLOBALS cache for courses?
  333. // If it is, then load from that.
  334. if ($bool_load_from_global_cache == true &&
  335. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["subject_id"] != "")
  336. {
  337. $subject_id = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["subject_id"];
  338. $course_num = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["course_num"];
  339. $title = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["title"];
  340. $description = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["description"];
  341. $min_hours = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["min_hours"];
  342. $max_hours = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["max_hours"];
  343. $repeat_hours = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["repeat_hours"];
  344. $db_exclude = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["db_exclude"];
  345. $array_valid_names = $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["array_valid_names"];
  346. // load this into the course object, if not null.
  347. return;
  348. }
  349. if ($course_id != 0)
  350. {
  351. $res = $this->db_query("SELECT * FROM courses
  352. WHERE course_id = '?'
  353. AND catalog_year = '?'
  354. AND catalog_year <= '?'
  355. AND delete_flag = '0'
  356. AND exclude = '0' ", $course_id, $catalog_year, $current_catalog_year);
  357. $cur = $this->db_fetch_array($res);
  358. if ($this->db_num_rows($res) < 1)
  359. {
  360. // No results found, so instead pick the most recent
  361. // catalog year that is not excluded (keeping below the
  362. // current catalog year from the settings)
  363. //$this2 = new DatabaseHandler();
  364. $res2 = $this->db_query("SELECT * FROM courses
  365. WHERE `course_id`='?'
  366. AND `subject_id`!=''
  367. AND `delete_flag` = '0'
  368. AND `exclude`='0'
  369. AND `catalog_year` <= '?'
  370. ORDER BY `catalog_year` DESC LIMIT 1", $course_id, $current_catalog_year);
  371. $cur = $this->db_fetch_array($res2);
  372. if ($this->db_num_rows($res2) < 1)
  373. {
  374. // Meaning, there were no results found that didn't have
  375. // the exclude flag set. So, as a last-ditch effort,
  376. // go ahead and try to retrieve any course, even if it has
  377. // been excluded. (keeping below the
  378. // current catalog year from the settings)
  379. //$this3 = new DatabaseHandler();
  380. //
  381. $res3 = $this->db_query("SELECT * FROM courses
  382. WHERE course_id = '?'
  383. AND subject_id != ''
  384. AND delete_flag = '0'
  385. AND catalog_year <= '?'
  386. ORDER BY catalog_year DESC LIMIT 1", $course_id, $current_catalog_year);
  387. $cur = $this->db_fetch_array($res3);
  388. }
  389. }
  390. $title = $cur["title"];
  391. $description = trim($cur["description"]);
  392. $subject_id = trim(strtoupper($cur["subject_id"]));
  393. $course_num = trim(strtoupper($cur["course_num"]));
  394. if ($min_hours < 1)
  395. {
  396. $min_hours = $cur["min_hours"];
  397. $max_hours = $cur["max_hours"];
  398. $repeat_hours = $cur["repeat_hours"];
  399. if ($repeat_hours*1 < 1)
  400. {
  401. $repeat_hours = $max_hours;
  402. }
  403. }
  404. $db_exclude = $cur["exclude"];
  405. $data_entry_comment = $cur["data_entry_comment"];
  406. // Now, lets get a list of all the valid names for this course.
  407. // In other words, all the non-excluded names. For most
  408. // courses, this will just be one name. But for cross-listed
  409. // courses, this will be 2 or more (probably just 2 though).
  410. // Example: MATH 373 and CSCI 373 are both valid names for that course.
  411. $res = $this->db_query("SELECT * FROM courses
  412. WHERE course_id = '?'
  413. AND exclude = '0' ", $course_id);
  414. while($cur = $this->db_fetch_array($res))
  415. {
  416. $si = $cur["subject_id"];
  417. $cn = $cur["course_num"];
  418. if (in_array("$si~$cn", $array_valid_names))
  419. {
  420. continue;
  421. }
  422. $array_valid_names[] = "$si~$cn";
  423. }
  424. }
  425. if ($description == "")
  426. {
  427. $description = "There is no course description available at this time.";
  428. }
  429. if ($title == "")
  430. {
  431. $title = "$subject_id $course_num";
  432. }
  433. // Now, to reduce the number of database calls in the future, save this
  434. // to our GLOBALS cache...
  435. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["subject_id"] = $subject_id;
  436. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["course_num"] = $course_num;
  437. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["title"] = $title;
  438. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["description"] = $description;
  439. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["min_hours"] = $min_hours;
  440. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["max_hours"] = $max_hours;
  441. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["repeat_hours"] = $repeat_hours;
  442. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["db_exclude"] = $db_exclude;
  443. $GLOBALS["fp_course_inventory"][$course_id][$cache_catalog_year]["array_valid_names"] = $array_valid_names;
  444. $GLOBALS["cache_course_inventory"] = true; // rebuild this cache before it closes.
  445. // Should we put all this into our course object?
  446. }
  447. function duplicate_course_for_year($course = null, $catalog_year)
  448. {
  449. // Duplicate the course for the given catalog_year.
  450. // If it already exists for that catalog_year, delete it from the
  451. // table.
  452. // In other words, copy all course data from some valid year into this
  453. // new year.
  454. $c = $course;
  455. $course_id = $c->course_id;
  456. $min_hours = $c->min_hours;
  457. $max_hours = $c->max_hours;
  458. if ($c->bool_ghost_min_hour) {
  459. $min_hours = 0;
  460. }
  461. if ($c->bool_ghost_max_hour) {
  462. $max_hours = 0;
  463. }
  464. $res = $this->db_query("DELETE FROM draft_courses WHERE
  465. course_id = '?' AND catalog_year = '?'
  466. AND subject_id = '?'
  467. AND course_num = '?' ", $course_id, $catalog_year, $c->subject_id, $c->course_num);
  468. $res2 = $this->db_query("INSERT INTO draft_courses(course_id,
  469. subject_id, course_num, catalog_year,
  470. title, description, min_hours, max_hours,
  471. repeat_hours, exclude) values (
  472. '?','?','?','?','?','?','?','?','?','?')
  473. ", $course_id, $c->subject_id,$c->course_num,$catalog_year,$c->title,$c->description,$min_hours,$max_hours,$c->repeat_hours,$c->db_exclude);
  474. }
  475. function update_course_requirement_from_name($subject_id, $course_num, $new_course_id)
  476. {
  477. // This will convert all instances of subject_id/course_num
  478. // to use the newCourseID. It looks through the requirements tables
  479. // that may have listed it as a requirement. We will
  480. // look specifically at the data_entry_value to do some of them.
  481. // ************ IMPORTANT ****************
  482. // This is used only by dataentry. It is intentionally
  483. // not doing the draft tables!
  484. $res = $this->db_query("UPDATE degree_requirements
  485. set `course_id`='?'
  486. where `data_entry_value`='?~?' ", $new_course_id, $subject_id, $course_num) ;
  487. $res = $this->db_query("UPDATE group_requirements
  488. SET `course_id`='?'
  489. WHERE `data_entry_value`='?~?' ", $new_course_id, $subject_id, $course_num) ;
  490. // Also update substitutions....
  491. $res = $this->db_query("UPDATE student_substitutions
  492. SET `sub_course_id`='?'
  493. WHERE `sub_entry_value`='?~?' ", $new_course_id, $subject_id, $course_num) ;
  494. $res = $this->db_query("UPDATE student_substitutions
  495. SET `required_course_id`='?'
  496. WHERE `required_entry_value`='?~?' ", $new_course_id, $subject_id, $course_num) ;
  497. // Also the advising histories....
  498. $res = $this->db_query("UPDATE advised_courses
  499. SET `course_id`='?'
  500. WHERE `entry_value`='?~?' ", $new_course_id, $subject_id, $course_num) ;
  501. }
  502. function add_draft_instruction($text)
  503. {
  504. // Adds a new "instruction" to the draft_instructions table.
  505. // Simple insert.
  506. $res = $this->db_query("INSERT INTO draft_instructions
  507. (instruction) VALUES ('?') ", $text);
  508. }
  509. function update_course_id($from_course_id, $to_course_id, $bool_draft = false)
  510. {
  511. // This will convert *all* instances of "fromCourseID"
  512. // across every table that it is used, to toCourseID.
  513. // Use this function when you want to change a course's
  514. // course_id in the database.
  515. $table_array = array("advised_courses",
  516. "courses",
  517. "degree_requirements",
  518. "group_requirements",
  519. "student_unassign_group");
  520. if ($bool_draft)
  521. { // only do the draft tables...
  522. $table_array = array(
  523. "draft_courses",
  524. "draft_degree_requirements",
  525. "draft_group_requirements",
  526. );
  527. }
  528. // Do the tables where it's named "course_id"...
  529. foreach($table_array as $table_name)
  530. {
  531. $res = $this->db_query("UPDATE $table_name
  532. SET course_id = '?'
  533. WHERE course_id = '?' ", $to_course_id, $from_course_id);
  534. }
  535. $res = $this->db_query("update student_substitutions
  536. set `required_course_id`='?'
  537. where `required_course_id`='?' ", $to_course_id, $from_course_id);
  538. $res = $this->db_query("update student_substitutions
  539. set `sub_course_id`='?'
  540. where `sub_course_id`='?'
  541. and `sub_transfer_flag`='0' ", $to_course_id, $from_course_id);
  542. $res = $this->db_query("update transfer_eqv_per_student
  543. set `local_course_id`='?'
  544. where `local_course_id`='?' ", $to_course_id, $from_course_id);
  545. }
  546. function get_advising_session_id($faculty_id = 0, $student_id = "", $term_id = "", $degree_id = "", $bool_what_if = false, $bool_draft = true)
  547. {
  548. $is_what_if = "0";
  549. $is_draft = "0";
  550. $draft_line = " and `is_draft`='$is_draft' ";
  551. $faculty_line = " and `faculty_id`='$faculty_id' ";
  552. if ($faculty_id == 0)
  553. { // If no faculty is specified, just get the first one to come up.
  554. $faculty_line = "";
  555. }
  556. if ($bool_what_if == true){$is_what_if = "1";}
  557. if ($bool_draft == true)
  558. {
  559. $is_draft = "1";
  560. $draft_line = "";
  561. // If we are told to pull up draft, we can safely
  562. // assume we just want the most recent save, whether it
  563. // is saved as a draft or not.
  564. }
  565. $query = "select * from advising_sessions
  566. where
  567. `student_id`='$student_id'
  568. $faculty_line
  569. and `term_id`='$term_id'
  570. and `degree_id`='$degree_id'
  571. and `is_whatif`='$is_what_if'
  572. $draft_line
  573. order by `posted` desc limit 1";
  574. $result = $this->db_query($query) ;
  575. if ($this->db_num_rows($result) > 0)
  576. {
  577. $cur = $this->db_fetch_array($result);
  578. $advising_session_id = $cur["advising_session_id"];
  579. return $advising_session_id;
  580. }
  581. return 0;
  582. }
  583. function get_group_id($group_name, $catalog_year)
  584. {
  585. if ($catalog_year < $GLOBALS["fp_system_settings"]["earliest_catalog_year"])
  586. {
  587. $catalog_year = $GLOBALS["fp_system_settings"]["earliest_catalog_year"];
  588. }
  589. $res7 = $this->db_query("SELECT * FROM groups
  590. WHERE `group_name`='?'
  591. AND `catalog_year`='?'
  592. LIMIT 1 ", $group_name, $catalog_year) ;
  593. if ($this->db_num_rows($res7) > 0)
  594. {
  595. $cur7 = $this->db_fetch_array($res7);
  596. return $cur7["group_id"];
  597. }
  598. return false;
  599. }
  600. function request_new_degree_id()
  601. {
  602. // Return a valid new id...
  603. for ($t = 0; $t < 100; $t++)
  604. {
  605. $id = mt_rand(1,9999999);
  606. // Check for collisions...
  607. $res4 = $this->db_query("SELECT * FROM draft_degrees
  608. WHERE `degree_id`='$id' limit 1");
  609. if ($this->db_num_rows($res4) == 0)
  610. { // Was not in the table already, so use it!
  611. return $id;
  612. }
  613. }
  614. return false;
  615. }
  616. function get_institution_name($institution_id)
  617. {
  618. // Return the name of the institution...
  619. $res = $this->db_query("SELECT * FROM transfer_institutions
  620. where institution_id = '?' ", $institution_id);
  621. $cur = $this->db_fetch_array($res);
  622. return trim($cur['name']);
  623. }
  624. /**
  625. * Retrieve a value from the variables table.
  626. *
  627. * @param string $name
  628. */
  629. function get_variable($name, $default_value = "") {
  630. $res = $this->db_query("SELECT value FROM variables
  631. WHERE name = '?' ", $name);
  632. $cur = $this->db_fetch_array($res);
  633. $val = $cur["value"];
  634. if ($val == "") {
  635. $val = $default_value;
  636. }
  637. return $val;
  638. }
  639. /**
  640. * Sets a variable's value in the variables table.
  641. *
  642. * @param unknown_type $name
  643. * @param unknown_type $value
  644. */
  645. function set_variable($name, $value) {
  646. $res2 = $this->db_query("REPLACE INTO variables (name, value)
  647. VALUES ('?', '?') ", $name, $value);
  648. }
  649. function get_course_id($subject_id, $course_num, $catalog_year = "", $bool_use_draft = false)
  650. {
  651. // Ignore the colon, if there is one.
  652. if (strpos($course_num,":"))
  653. {
  654. //$course_num = substr($course_num,0,-2);
  655. $temp = explode(":", $course_num);
  656. $course_num = trim($temp[0]);
  657. }
  658. // Always override if the global variable is set.
  659. if ($GLOBALS["fp_advising"]["bool_use_draft"] == true) {
  660. $bool_use_draft = true;
  661. }
  662. $catalog_line = "";
  663. if ($catalog_year != "")
  664. {
  665. $catalog_line = "and `catalog_year`='$catalog_year' ";
  666. }
  667. $table_name = "courses";
  668. if ($bool_use_draft){$table_name = "draft_$table_name";}
  669. $res7 = $this->db_query("SELECT * FROM $table_name
  670. WHERE subject_id = '?'
  671. AND course_num = '?'
  672. $catalog_line
  673. ORDER BY catalog_year DESC LIMIT 1 ", $subject_id, $course_num) ;
  674. if ($this->db_num_rows($res7) > 0)
  675. {
  676. $cur7 = $this->db_fetch_array($res7);
  677. return $cur7["course_id"];
  678. }
  679. return false;
  680. }
  681. function get_student_settings($student_cwid)
  682. {
  683. // This returns an array (from the xml) of a student's
  684. // settings in the student_settings table. It will
  685. // return FALSE if the student was not in the table.
  686. $res = $this->db_query("SELECT * FROM student_settings
  687. WHERE student_id = '?' ", $student_cwid) ;
  688. if ($this->db_num_rows($res) < 1)
  689. {
  690. return false;
  691. }
  692. $cur = $this->db_fetch_array($res);
  693. if (!$rtn = unserialize($cur["settings"])) {
  694. $rtn = array();
  695. }
  696. return $rtn;
  697. }
  698. function get_student_cumulative_hours($student_cwid) {
  699. // Let's perform our queries.
  700. $res = $this->db_query("SELECT * FROM students
  701. WHERE cwid = '?' ", $student_cwid);
  702. $cur = $this->db_fetch_array($res);
  703. return $cur["cumulative_hours"];
  704. }
  705. function get_student_gpa($student_cwid) {
  706. // Let's perform our queries.
  707. $res = $this->db_query("SELECT * FROM students
  708. WHERE cwid = '?' ", $student_cwid);
  709. $cur = $this->db_fetch_array($res);
  710. return $cur["gpa"];
  711. }
  712. function get_student_catalog_year($student_cwid) {
  713. // Let's perform our queries.
  714. $res = $this->db_query("SELECT * FROM students
  715. WHERE cwid = '?' ", $student_cwid);
  716. $cur = $this->db_fetch_array($res);
  717. $catalog = $cur["catalog_year"];
  718. $temp = explode("-", $catalog);
  719. return trim($temp[0]);
  720. }
  721. /**
  722. * Returns whatever is in the Rank field for this student.
  723. * Ex: JR, SR, FR, etc.
  724. *
  725. * @param unknown_type $student_id
  726. * @return unknown
  727. */
  728. function get_student_rank($student_cwid) {
  729. // Let's perform our queries.
  730. $res = $this->db_query("SELECT * FROM students
  731. WHERE cwid = '?' ", $student_cwid);
  732. $cur = $this->db_fetch_array($res);
  733. $rank = $cur["rank_code"];
  734. return trim($rank);
  735. }
  736. /**
  737. * Returns the student's first and last name, put together.
  738. * Ex: John Smith or John W Smith.
  739. *
  740. * @param int $student_id
  741. * @return string
  742. */
  743. function get_student_name($cwid) {
  744. // Let's perform our queries.
  745. $res = $this->db_query("SELECT * FROM users
  746. WHERE cwid = '?'
  747. AND is_student = 1 ", $cwid);
  748. $cur = $this->db_fetch_array($res);
  749. $name = $cur["f_name"] . " " . $cur["l_name"];
  750. // Force into pretty capitalization.
  751. // turns JOHN SMITH into John Smith
  752. $name = ucwords(strtolower($name));
  753. return trim($name);
  754. }
  755. /**
  756. * Returns the faculty's first and last name, put together.
  757. * Ex: John Smith or John W Smith.
  758. *
  759. * @param int $faculty_id
  760. * @return string
  761. */
  762. function get_faculty_name($cwid) {
  763. // Let's pull the needed variables out of our settings, so we know what
  764. // to query, because this is a non-FlightPath table.
  765. //$tsettings = $GLOBALS["fp_system_settings"]["extra_tables"]["human_resources:faculty_staff"];
  766. //$tf = (object) $tsettings["fields"]; //Convert to object, makes it easier to work with.
  767. //$table_name = $tsettings["table_name"];
  768. // Let's perform our queries.
  769. $res = $this->db_query("SELECT * FROM users
  770. WHERE cwid = '?'
  771. AND is_faculty = '1' ", $cwid);
  772. $cur = $this->db_fetch_array($res);
  773. $name = $cur["f_name"] . " " . $cur["l_name"];
  774. // Force into pretty capitalization.
  775. // turns JOHN SMITH into John Smith
  776. $name = ucwords(strtolower($name));
  777. return trim($name);
  778. }
  779. /**
  780. * Looks in our extra tables to find out what major code, if any, has been assigned
  781. * to this faculty member.
  782. *
  783. */
  784. function get_faculty_major_code($faculty_cwid) {
  785. // Let's pull the needed variables out of our settings, so we know what
  786. // to query, because this is a non-FlightPath table.
  787. //$tsettings = $GLOBALS["fp_system_settings"]["extra_tables"]["human_resources:faculty_staff"];
  788. //$tf = (object) $tsettings["fields"]; //Convert to object, makes it easier to work with.
  789. //$table_name = $tsettings["table_name"];
  790. $res = $this->db_query("SELECT * FROM faculty WHERE cwid = '?' ", $faculty_cwid);
  791. $cur = $this->db_fetch_array($res);
  792. return $cur["major_code"];
  793. }
  794. function get_student_major_from_db($student_cwid)
  795. {
  796. // Returns the student's major code from the DB. Does not
  797. // return the track code.
  798. // Let's perform our queries.
  799. $res = $this->db_query("SELECT * FROM students
  800. WHERE cwid = '?' ", $student_cwid);
  801. $cur = $this->db_fetch_array($res);
  802. return trim($cur["major_code"]);
  803. }
  804. function get_flightpath_settings()
  805. {
  806. // Returns an array of everything in the flightpath_settings table.
  807. $rtn_array = array();
  808. $res = $this->db_query("SELECT * FROM flightpath_settings ") ;
  809. while($cur = $this->db_fetch_array($res))
  810. {
  811. $rtn_array[$cur["variable_name"]] = trim($cur["value"]);
  812. }
  813. return $rtn_array;
  814. }
  815. function get_degrees_in_catalog_year($catalog_year, $bool_include_tracks = false, $bool_use_draft = false, $bool_undergrad_only = TRUE)
  816. {
  817. // Returns an array of all the degrees from a particular year
  818. // which are entered into FlightPath.
  819. $table_name = "degrees";
  820. if ($bool_use_draft){$table_name = "draft_$table_name";}
  821. if ($bool_undergrad_only) $undergrad_line = "AND degree_class != 'G' ";
  822. $rtn_array = array();
  823. $res = $this->db_query("SELECT * FROM $table_name
  824. WHERE catalog_year = '?'
  825. AND exclude = '0'
  826. $undergrad_line
  827. ORDER BY title, major_code ", $catalog_year);
  828. if ($this->db_num_rows($res) < 1)
  829. {
  830. return false;
  831. }
  832. while ($cur = $this->db_fetch_array($res))
  833. {
  834. $degree_id = $cur["degree_id"];
  835. $major = trim($cur["major_code"]);
  836. $title = trim($cur["title"]);
  837. $track_code = "";
  838. $major_code = $major;
  839. // The major may have a track specified. If so, take out
  840. // the track and make it seperate.
  841. if (strstr($major, "_"))
  842. {
  843. $temp = explode("_", $major);
  844. $major_code = trim($temp[0]);
  845. $track_code = trim($temp[1]);
  846. // The major_code might now have a | at the very end. If so,
  847. // get rid of it.
  848. if (substr($major_code, strlen($major_code)-1, 1) == "|")
  849. {
  850. $major_code = str_replace("|","",$major_code);
  851. }
  852. }
  853. // Leave the track in if requested.
  854. if ($bool_include_tracks == true)
  855. {
  856. // Set it back to what we got from the db.
  857. $major_code = $major;
  858. $temp_degree = $this->get_degree_plan($major, $catalog_year, true);
  859. if ($temp_degree->track_code != "")
  860. {
  861. $title .= " - " . $temp_degree->track_title;
  862. }
  863. }
  864. $rtn_array[$major_code]["title"] = $title;
  865. $rtn_array[$major_code]["degree_id"] = $degree_id;
  866. $rtn_array[$major_code]["degree_class"] = trim(strtoupper($cur["degree_class"]));
  867. }
  868. return $rtn_array;
  869. }
  870. function get_degree_tracks($major_code, $catalog_year)
  871. {
  872. // Will return an array of all the tracks that a particular major
  873. // has. Must match the major_code in degree_tracks table.
  874. // Returns FALSE if there are none.
  875. $rtn_array = array();
  876. $res = $this->db_query("SELECT * FROM degree_tracks
  877. WHERE major_code = '?'
  878. AND catalog_year = '?' ", $major_code, $catalog_year);
  879. if ($this->db_num_rows($res) < 1)
  880. {
  881. return false;
  882. }
  883. while($cur = $this->db_fetch_array($res))
  884. {
  885. extract($cur, 3, "db");
  886. $rtn_array[] = $db_track_code;
  887. }
  888. return $rtn_array;
  889. }
  890. function get_degree_plan($major_and_track_code, $catalog_year = "", $bool_minimal = false)
  891. {
  892. // Returns a degreePlan object from the supplied information.
  893. // If catalog_year is blank, use whatever the current catalog year is, loaded from our settings table.
  894. if ($catalog_year == "") {
  895. $catalog_year = variable_get("current_catalog_year", "2006");
  896. }
  897. $degree_id = $this->get_degree_id($major_and_track_code, $catalog_year);
  898. $dp = new DegreePlan($degree_id,null,$bool_minimal);
  899. if ($dp->major_code == "")
  900. {
  901. $dp->major_code = trim($major_and_track_code);
  902. }
  903. return $dp;
  904. }
  905. function get_degree_id($major_and_track_code, $catalog_year, $bool_use_draft = false)
  906. {
  907. // This function expects the major_code and track_code (if it exists)
  908. // to be joined using |_. Example:
  909. // GSBA|_123 or KIND|EXCP_231.
  910. // In other words, all in one.
  911. // Always override if the global variable is set.
  912. if ($GLOBALS["fp_advising"]["bool_use_draft"] == true) {
  913. $bool_use_draft = true;
  914. }
  915. if ($catalog_year < $GLOBALS["fp_system_settings"]["earliest_catalog_year"])
  916. { // Lowest possible year.
  917. $catalog_year = $GLOBALS["fp_system_settings"]["earliest_catalog_year"];
  918. }
  919. $table_name = "degrees";
  920. if ($bool_use_draft){$table_name = "draft_$table_name";}
  921. $res7 = $this->db_query("SELECT * FROM $table_name
  922. WHERE major_code = '?'
  923. AND catalog_year = '?'
  924. LIMIT 1 ", $major_and_track_code, $catalog_year) ;
  925. if ($this->db_num_rows($res7) > 0)
  926. {
  927. $cur7 = $this->db_fetch_array($res7);
  928. return $cur7["degree_id"];
  929. }
  930. return false;
  931. }
  932. function db_num_rows($result) {
  933. return mysql_num_rows($result);
  934. }
  935. function db_affected_rows() {
  936. return mysql_affected_rows();
  937. }
  938. function db_insert_id() {
  939. return mysql_insert_id();
  940. }
  941. function db_fetch_array($result) {
  942. return mysql_fetch_array($result);
  943. }
  944. function db_fetch_object($result) {
  945. return mysql_fetch_object($result);
  946. }
  947. function db_close() {
  948. return mysql_close($this->dbc);
  949. }
  950. }