<?php include_once '../lib/util.php'; $db = new DBAdmin(); $db->truncate('schedule'); echo 'Updating `schedule` table ... '; $q = $db->selectAll('htmldata'); $db->begin(); while ($row = $q->fetch_assoc()) { $schedules = trim(kana($row['schedule'])); if ($schedules === '') { $db->insert('schedule', array('id' => $row['id'])); } foreach (explode(' ', $schedules) as $schedule) { if ($schedule) { preg_match('/^(?:(集)|(日|月|火|水|木|金|土))?(?:(?:(\\d+)|(不定))' . '(後)?(?:~(\\d+)(前)?)?)?$/', $schedule, $m); if ($m) { $m = array_pad($m, 8, 0); for ($i = (int) $m[3]; $i <= max((int) $m[6], (int) $m[3]); $i++) { $data = array('id' => $row['id'], 'day' => $m[2] ? mb_strpos('日月火水木金土', $m[2]) : NULL, 'period' => $i ? $i : NULL, 'early' => $i == $m[6] && (bool) $m[7], 'late' => $i == $m[3] && (bool) $m[5], 'intensive' => (bool) $m[1], 'irregular' => (bool) $m[4], 'description' => $schedule); $db->insert('schedule', $data); } } else { echo "{$schedule}\n"; } } } echo "[31G[K{$row['id']}"; } $db->commit(); $db->close();
<?php include_once '../lib/util.php'; $db = new DBAdmin(); echo 'Updating `json` table ... '; $q = $db->selectAll('summary'); $db->begin(); while ($row = $q->fetch_assoc()) { $department = $db->single('SELECT * FROM `department` WHERE `department_id` = ?', $row['department_id']); $icode = $db->single('SELECT `internal_code` FROM `list` WHERE `id` = ?', $row['id']); $teacher = array_map(function ($t) { return implode($t); }, $db->query('SELECT `staff`.`name` FROM `teacher` INNER JOIN `staff` ON `staff`.`staff_id` = `teacher`.`staff_id` WHERE `id` = ? ORDER BY `teacher`.`main` DESC', $row['id'])->fetch_all(MYSQL_ASSOC)); $semester = NULL; if (is_null($row['semester_id']) === FALSE) { $semester = $db->single('SELECT `description` FROM `semester` WHERE `semester_id` = ?', $row['semester_id']); } $schedule = array_map(function ($a) { return array('day' => is_null($a['day']) ? NULL : (int) $a['day'], 'period' => is_null($a['period']) ? NULL : (int) $a['period'], 'early' => (bool) $a['early'], 'late' => (bool) $a['late'], 'intensive' => (bool) $a['intensive'], 'irregular' => (bool) $a['irregular']); }, $db->query('SELECT * FROM `schedule` WHERE `id` = ?', $row['id'])->fetch_all(MYSQL_ASSOC)); $classroom = $db->query('SELECT `department`.`name` as `place`, `department`.`department_code` as `place_code`, `room`.`name` as `name` FROM `classroom`INNER JOIN `room` ON `room`.`room_id` = `classroom`.`room_id` INNER JOIN `department` ON `department`.`department_id` = `room`.`department_id` WHERE `classroom`.`id` = ?', $row['id'])->fetch_all(MYSQL_ASSOC);
<?php include_once '../lib/util.php'; $db = new DBAdmin(); $begin = isset($argv[1]) ? (int) $argv[1] : 0; $end = isset($argv[2]) ? $argv[2] : NULL; echo 'Updating `raw` table ... '; $year = $db->single("SELECT MAX(`value`) FROM `config`\n \tWHERE `name` = 'year'"); $q = $db->query('SELECT * FROM `list` WHERE `year` = ? ORDER BY `id`', $year); while ($row = $q->fetch_assoc()) { if ($begin <= $row['id'] && (is_null($end) || $row['id'] <= $end)) { $query = "?NENDO={$row['year']}&BUKYOKU=" . "{$row['department_code']}&CODE={$row['internal_code']}"; $html = sjis2utf(file_get_contents(HTML_URL . $query)); $text = sjis2utf(file_get_contents(TEXT_URL . $query)); if (mb_strpos($text, '<TITLE>授業が見つかりません</TITLE>') !== FALSE) { continue; } $db->replace('raw', array('id' => $row['id'], 'html' => $html, 'text' => $text)); } echo "[26G[K{$row['id']}"; } $db->close(); echo " " . PRINT_OK . PHP_EOL;
<?php include_once '../lib/util.php'; $db = new DBAdmin(); echo 'Updating `summary` table ... '; $table = $db->query('SELECT `list`.`id`, `list`.`year`, `department`.`department_id`, `htmldata`.`code`, `htmldata`.`title`, `htmldata`.`title_english`, `htmldata`.`credit`, `htmldata`.`target`, `htmldata`.`style`, `htmldata`.`note`, `htmldata`.`public`, `htmldata`.`ches` FROM `list` LEFT JOIN `htmldata` ON `list`.`id` = `htmldata`.`id` LEFT JOIN `department` ON `list`.`department_code` = `department`.`department_code` ORDER BY `list`.`id`'); $db->begin(); while ($row = $table->fetch_assoc()) { $row = array_map(function ($s) { return trim(kana($s)); }, $row); if ($row['code'] === '') { $row['code'] = NULL; } $db->replace('summary', $row); echo "[30G[K{$row['id']}"; } $db->commit(); $db->close(); echo " " . PRINT_OK . PHP_EOL;
<?php include_once '../lib/util.php'; $db = new DBAdmin(); /* トップページの取得 */ echo 'Downloading top page ... '; $src = file_get_contents(SEARCH_URL); echo PRINT_OK . PHP_EOL; $html = htmlobject($src); $tr = $html->body->form->table->tbody->tr->td->table->tbody->tr; $year = (int) $tr[1]->td[1]->input['value']; $departments = $tr[0]->td[1]->select->option; /* 年度を更新 */ $db->query('INSERT INTO `config` (`name`, `value`) VALUES (\'year\', ?) ON DUPLICATE KEY UPDATE `name` = \'year\', `value` = ?', $year, $year); /* 部局リストの更新 */ echo 'Updating `department` table ... '; $db->begin(); foreach ($departments as $department) { $code = (string) $department['value']; if ($code) { $name = kana((string) $department); $db->insert('department', array('department_code' => $code, 'name' => $name)); } } $db->commit(); echo PRINT_OK . PHP_EOL; /* 講義データのリストを取得 */ echo 'Updating `list` table ... '; $postdata = array('MODE' => 0, 'STARTNO' => 0, 'NENDO' => $year, 'CODE_JYOUKEN' => 0, 'BtKENSAKU' => 0); for ($i = 0, $j = -1;; $j = -1) {
<?php include_once '../lib/util.php'; $db = new DBAdmin(); $db->truncate('textdata'); echo 'Updating `textdata` table ... '; $q = $db->query('SELECT `id`, `text` FROM `raw`'); $db->begin(); while ($row = $q->fetch_assoc()) { /* CRLFをLFに統一 */ $text = str_replace("\r\n", "\n", $row['text']); /* それぞれの項目に区切る */ $paragraphs = preg_split('/\\-{50}\\r?\\n(?=【.*?】\\r?\\n\\-{50}\\r?\\n)/', $text); array_shift($paragraphs); $data = array(); foreach ($paragraphs as $i => $paragraph) { /* keyとvalueを取り出す */ $pattern = '/^【(.*?)】\\n(?:\\-{50}\\n\\s*([\\s\\S]*?))?\\s*$/'; preg_match($pattern, $paragraph, $matches); if (isset($matches[1]) === FALSE) { $matches[1] = ''; } if (isset($matches[2]) === FALSE) { $matches[2] = NULL; } list(, $key, $value) = $matches; if (!$key && !$value) { continue; } /* 英数字を半角に、カタカナを全角に変換 */ $key = trim(kana($key));
<?php include_once '../lib/util.php'; $db = new DBAdmin(); echo 'Updating `htmldata` table ... '; $q = $db->query('SELECT `id`, `html` FROM `raw`'); $db->begin(); while ($row = $q->fetch_assoc()) { /* CRLFをLFに統一 */ $src = str_replace("\r\n", "\n", $row['html']); /* 文字コードを書き換え */ $src = str_replace('charset=Shift_JIS', 'charset=utf-8', $src); /* HTMLをオブジェクトに変換 */ $html = htmlobject($src); /* 抽出 */ $data = array('id' => $row['id']); $tmp = $html->body->center->table->tbody->tr->td->div; $data['code'] = $tmp[1]->table->tbody->tr[0]->td[3]; $data['public'] = (int) (strpos($tmp[0], '市民開放授業') !== FALSE); $data['ches'] = (int) (strpos($tmp[0], '県内大学') !== FALSE); $tmp = $tmp[1]->table->tbody->tr; $data['title'] = $tmp[1]->td[1]; if (substr($data['code'], 0, 1) !== 'M') { $data['title_english'] = $tmp[2]->td; $data['teacher'] = $tmp[3]->td[1]; $data['sub_teacher'] = $tmp[3]->td[3]; $data['semester'] = $tmp[4]->td[1]; $data['schedule'] = $tmp[4]->td[3]; $data['classroom'] = $tmp[4]->td[5]; $data['credit'] = $tmp[4]->td[7]; $data['target'] = $tmp[5]->td[1];
<?php include_once '../lib/util.php'; $db = new DBAdmin(); function insertTeacher($db, $id, $name, $main) { $q = $db->query('SELECT `staff_id` FROM `staff` WHERE `name` = ?', $name); if ($q) { $res = $q->fetch_assoc(); if ($res) { $db->insert('teacher', array('id' => $id, 'staff_id' => $res['staff_id'], 'main' => $main)); } } } echo 'Updating `teacher` table ... '; $q = $db->selectAll('htmldata'); $db->begin(); while ($row = $q->fetch_assoc()) { $teacher = trim($row['teacher']); if ($teacher) { $teachers = preg_split('/ |,| (?![A-Z])/', $teacher); foreach ($teachers as $i => $teacher) { $teacher = trim(kana($teacher)); if ($teacher && $teacher !== '他') { $db->insert('staff', array('name' => $teacher)); insertTeacher($db, $row['id'], $teacher, !$i); } } } $sub = $row['sub_teacher']; $sub = kana($sub);
<?php include_once '../lib/util.php'; $db = new DBAdmin(); echo 'Updating `semester` table ... '; $q = $db->selectAll('htmldata'); $db->begin(); while ($row = $q->fetch_assoc()) { $desc = trim(kana($row['semester'])); if ($desc) { $full = strpos($desc, '通年') !== FALSE; $first = $full || strpos($desc, '前期') !== FALSE; $second = $full || strpos($desc, '後期') !== FALSE; $intensive = strpos($desc, '集中') !== FALSE; $db->insert('semester', array('first' => $first, 'second' => $second, 'intensive' => $intensive, 'description' => $desc)); } $db->query('UPDATE `summary` SET `semester_id` = (SELECT `semester_id` FROM `semester` WHERE `description` = ?) WHERE `id` = ?', $desc, $row['id']); } $db->commit(); $db->close(); echo " " . PRINT_OK . PHP_EOL;
<?php include_once '../lib/util.php'; $db = new DBAdmin(); $db->truncate('room'); $db->truncate('classroom'); echo 'Updating `classroom` table ... '; $q = $db->query('SELECT `list`.`id`, `list`.`place`, `htmldata`.`classroom` FROM `list` JOIN `htmldata` ON `list`.`id` = `htmldata`.`id`'); $db->begin(); while ($row = $q->fetch_assoc()) { $place = kana($row['place']); $rooms = trim(kana($row['classroom'])); $did = $db->single('SELECT `department_id` FROM `department` WHERE `name` = ?', $place); if (is_null($did)) { if ($place) { $did = $db->single('SELECT `department_id` FROM `department` WHERE ' . implode(' AND ', array_map(function ($s) use($db) { return "`name` LIKE '%" . $db->escape($s) . "%'"; }, preg_split('/\\(|\\)/', $place)))); } } foreach (explode(' ', $rooms) as $room) { if (is_null($did) && !$room) { continue; } $db->insert('room', array('department_id' => $did, 'name' => $room)); $did = is_null($did) ? 'IS NULL' : "= '" . $db->escape($did) . "'"; $rid = $db->single("SELECT `room_id` FROM `room`\n\t\t\tWHERE `department_id` {$did} AND `name` = ?", $room); $db->insert('classroom', array('id' => $row['id'], 'room_id' => $rid)); }