function getContactType($domestic='t') { $sql = "SELECT type, count(code) FROM contact WHERE type!='MOVIE' AND domestic='$domestic' GROUP BY type ORDER BY count DESC;"; return $this->arrayFromSql($sql); } function getContact($npp, $page, $domestic='t') { $offset = $npp * ($page-1); $sql = "SELECT count(code) FROM contact WHERE domestic='$domestic'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM contact WHERE domestic='$domestic' "; $sql .= " ORDER BY code ASC "; $sql .= " LIMIT ".(int)$npp ." OFFSET ".(int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getContactFromType($type, $npp=25, $page=1, $domestic='t') { $offset = $npp * ($page-1); $sql = "SELECT count(code) FROM contact WHERE type='$type' AND domestic='$domestic'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM contact WHERE type='$type' AND domestic='$domestic' "; $sql .= " ORDER BY code ASC "; $sql .= " LIMIT ".(int)$npp ." OFFSET ".(int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getContactFromCode($code, $use_cache=USE_CACHE) { $sql = "SELECT * FROM contact WHERE active = 't' AND code = '".(int)$code."'; "; if( $rows = $this->arrayFromSql($sql, $use_cache)){ return $rows[0]; } return FALSE; } /* function getContactFromMovieKey($movie_key) { $sql = "SELECT c.* FROM contact c "; $sql .= " WHERE c.movie_key = '".$movie_key."'"; $sql .= " ORDER BY c.domestic; "; return $this->arrayFromSql($sql, USE_CACHE); } */ function getContactFromMovieKey($movie_key, $domestic='t') { $sql = "SELECT c.*, l.priority, l.memo_ja, l.memo_en FROM linkage l LEFT JOIN company c ON l.code_company = c.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND code_company > 0 "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getFesList() { $sql = "SELECT * FROM fes ORDER BY priority; "; return $this->arrayFromSql($sql); } function insertFes($title_ja, $title_en) { $sql = 'SELECT max(priority) FROM fes; '; $n = $this->getOne($sql); $sql = 'INSERT INTO fes (title_ja, title_en, priority) '; $sql .= "VALUES ('".$this->sanitize(addslashes($title_ja))."','".$this->sanitize(addslashes($title_en))."', '".($n+1)."'); "; return $this->exec($sql); } function updateFesList($fesArray) { $sql = ''; $i = 0; foreach($fesArray as $a){ $sql .= "UPDATE fes SET title_ja = '".$this->sanitize(addslashes($a['FES_TITLE_JA']))."', title_en = '".$this->sanitize(addslashes($a['FES_TITLE_EN']))."', priority = '".$i."' WHERE code = '".$a['FES_CODE']."'; "; $i++; } return $this->exec($sql); } function getGenreFromMovieKey($key) { $sql = "SELECT g.* FROM genre g LEFT JOIN linkage l ON g.code = l.code_genre "; $sql .= "WHERE movie_key = '".$key."';"; return $this->arrayFromSql($sql); } function getGenreList($layer=GENRE_TYPE_GENRE) { $sql = "SELECT * FROM genre WHERE layer='".(int)$layer."' ORDER BY priority; "; return $this->arrayFromSql($sql); } function getGenreListWithCount($layer=GENRE_TYPE_GENRE) { $sql = "SELECT g.code, g.title_ja, g.title_en, count(l.code) FROM genre g "; $sql .= " LEFT JOIN linkage l ON g.code = l.code_genre "; if($layer>0) $sql .= " WHERE g.layer='".(int)$layer."' "; $sql .= " GROUP BY g.code, g.priority, g.title_ja, g.title_en ORDER BY g.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getAllGenreList() { $sql = "SELECT * FROM genre ORDER BY layer, priority; "; return $this->arrayFromSql($sql); } function insertGenre($title_ja, $title_en, $layer=GENRE_TYPE_GENRE) { global $gUser; $sql = 'SELECT max(priority) FROM genre; '; $n = $this->getOne($sql); $sql = 'INSERT INTO genre (layer, title_ja, title_en, priority, created_by, last_modified_by) '; $sql .= "VALUES ('".(int)$layer."', '".$this->sanitize($title_ja)."','".$this->sanitize($title_en)."', '".($n+1)."', '".(int)$gUser['code']."', '".(int)$gUser['code']."'); "; return $this->exec($sql); } function updateGenreList($genreArray, $layer=GENRE_TYPE_GENRE) { $sql = ''; $i = 0; foreach($genreArray as $a){ $sql .= "UPDATE genre SET title_ja = '".$this->sanitize($a['GENRE_TITLE_JA'])."', title_en = '".$this->sanitize($a['GENRE_TITLE_EN'])."', priority = '".$i."' WHERE layer='".$layer."' AND code = '".$a['GENRE_CODE']."'; "; $i++; } return $this->exec($sql); } function insertLinkage2Genre($movie_key, $genreArray) { $this->begin(); $sql = "DELETE FROM linkage WHERE code_genre > 0 AND movie_key = '".$movie_key."'; "; foreach($genreArray as $code_genre){ $sql .= "INSERT INTO linkage (movie_key, code_genre) VALUES ('".$movie_key."', '".$code_genre."'); "; } if( $this->exec($sql) ){ $this->end(); return true; }else{ $this->abort(); return false; } } function deleteGenreFromCode($code) { if($code<=0) return FALSE; $sql = "DELETE FROM genre WHERE code = '".(int)$code."'; "; return $this->exec($sql); } function getImgsFromMovieKey($key) { $sql = "SELECT * FROM imgs WHERE movie_key = '".$key."' AND path != '' AND active = 't' AND public = 't' ; "; return $this->arrayFromSql($sql, USE_CACHE); } function getUrlFromMovieKey($movie_key) { $sql = "SELECT * FROM url WHERE movie_key = '".$this->sanitize($movie_key)."' AND active = 't'; "; return $this->arrayFromSql($sql); } function getJFDBTopMovie() { $sql = "SELECT * FROM movie WHERE active='t' AND top = 't'; "; return $this->arrayFromSql($sql, USE_CACHE); } function getNofMovies() { $sql = "SELECT count(code) FROM movie;"; return $this->getOne($sql); } function getMovieFromDFJ($limit=25, $page=1) { $offset = $limit*($page-1); $sql = "SELECT count(key) FROM movie WHERE as_dfj = 't'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE as_dfj = 't' ORDER BY production_year DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); // // return $this->getMovieList($limit, $page, " as_dfj = 't' "); } function getMovie($limit=25, $page=1) { return $this->getMovieList($limit, $page); } function getMovieList($limit=25, $page=1, $opt='') { $offset = $limit*($page-1); $sql = "SELECT count(key) FROM movie WHERE active='t'"; if($opt) $sql .= ' AND '.$opt; $sql .= ';'; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' ".(($opt)?'AND'.$opt:'')." ORDER BY production_year DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getRecentMovies($limit=5, $offset=0) { $sql = "SELECT * FROM movie WHERE active='t' ORDER BY release_year DESC, release_month DESC, release_day DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromYear($year, $limit=25, $offset=0) { if($year<=1900) return FALSE; $sql = "SELECT count(key) FROM movie WHERE active='t' AND release_year = '".$year."'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND release_year = '".$year."' ORDER BY production_year DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMoviesFromProductionYear($year, $limit=25, $offset=0) { if($year<=1900) return FALSE; $sql = "SELECT count(key) FROM movie WHERE active='t' AND production_year = '".$year."'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND production_year = '".$year."' LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMoviesFromReleaseYear($year, $limit=25, $offset=0) { if($year<=1900) return FALSE; $sql = "SELECT count(key) FROM movie WHERE active='t' AND release_year = '".$year."'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND release_year = '".$year."' LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMovieFromIndexCharEn($char, $limit=25, $offset=0) { if(strlen($char)==0) return FALSE; $sql = "SELECT count(key) FROM movie WHERE active='t' AND title_for_search ~* '^".$char."'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND title_for_search ~* '^".$char."' ORDER BY title_en LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMovieFromIndexCharJa($char, $limit=25, $offset=0) { if(strlen($char)==0) return FALSE; $sql = "SELECT count(key) FROM movie WHERE active='t' AND title_roman ~* '^".$char."'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND title_roman ~* '^".$char."' ORDER BY title_yomi LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMovieFromIndexNum($limit=25, $offset=0) { $sql = "SELECT count(key) FROM movie WHERE active='t' AND title_for_search ~* '^[0-9]'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND title_for_search ~* '^[0-9]' ORDER BY title_en LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMovieFromIndexEtc($limit=25, $offset=0) { $sql = "SELECT count(key) FROM movie WHERE active='t' AND title_for_search !~* '^[0-9a-zA-Z]'; "; $total = $this->getOne($sql); $sql = "SELECT * FROM movie WHERE active='t' AND title_for_search !~* '^[0-9a-zA-Z]' ORDER BY title_en LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMovieFromGenre($code_genre, $limit=25, $page=1) { $offset = $limit*($page-1); $sql = "SELECT count(l.code) FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key WHERE m.active='t' AND l.code_genre = '".$code_genre."'; "; $total = $this->getOne($sql); $sql = "SELECT m.* FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key WHERE m.active='t' AND l.code_genre = '".(int)$code_genre."' "; $sql .= "ORDER BY production_year DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getMovieFromCode($code, $cache=USE_CACHE, $modePreview=FALSE) { $sql = "SELECT * FROM movie m WHERE m.code = '".(int)$code."' "; if($modePreview==FALSE) $sql .= " AND m.active = 't' "; $sql .= ';'; if($rows = $this->arrayFromSql($sql, $cache)){ return $rows[0]; } return false; } function getMovieFromKey($key, $cache=USE_CACHE, $modePreview=FALSE) { $sql = "SELECT * FROM movie m WHERE m.key = '".$this->sanitize($key)."' "; if($modePreview==FALSE) $sql .= " AND m.active = 't' "; $sql .= ';'; if($rows = $this->arrayFromSql($sql, $cache)){ return $rows[0]; } return false; } function getMovieFromPerson($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."'; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsProduction($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Production' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsProductionStudio($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Production_Studio' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsDistributor($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Distributor' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsDirector($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Director' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsProducer($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Producer' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsStaff($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Staff' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getMovieFromPersonAsCast($code_person) { $sql = 'SELECT m.*, l.description_ja, l.description_en FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key'; $sql .= " WHERE m.active='t' AND l.code_person = '".$code_person."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Cast' ) "; $sql .= " ORDER BY m.production_year DESC; "; return $this->arrayFromSql($sql, USE_CACHE); } function nMovieFromGenre($code_genre) { $sql = "SELECT count(l.code) FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key WHERE m.active='t' AND l.code_genre = '".$code_genre."'; "; return $this->getOne($sql); } function updateMovie($code, $P) { foreach($P as $k=>$v){ if(!is_array($v)) $P[$k] = $this->sanitize(addslashes($v)); } $key = md5($P['RELEASE_YEAR'].$P['TITLE_JA']); $sql = 'UPDATE movie SET '; // $sql .= "key = '".$key."', "; // $sql .= "release_year = '".$P['RELEASE_YEAR']."', "; $sql .= "title_en = '".$P['TITLE_EN']."', "; $sql .= "title_ja = '".$P['TITLE_JA']."', "; // $sql .= "title_org = '".$P['TITLE_ORG']."', "; // $sql .= "title_yomi = '".$P['TITLE_YOMI']."', "; $sql .= "description_en = '".$P['OUTLINE_EN']."', "; $sql .= "description_ja = '".$P['OUTLINE_JA']."' "; $sql .= "screen_format = '".$P['SCREEN_FORMAT']."', "; $sql .= "screen_size = '".$P['SCREEN_SIZE']."', "; $sql .= "screen_other = '".$P['SCREEN_OTHER']."', "; $sql .= "color = '".$P['COLOR']."', "; $sql .= "color_other = '".$P['COLOR']."', "; $sql .= "sound_system = '".$P['SOUND_SYSTEM']."', "; $sql .= "sound_process = '".$P['SOUND_PROCESS']."', "; $sql .= "sound_other = '".$P['SOUND_OTHER']."', "; $sql .= "rating = '".$P['RATING']."', "; // $sql .= "public = '".(($P['PUBLIC'][1]==1)?'t':'f')."' "; $sql .= "WHERE code = '".(int)$code."'; "; // $sql .= "UPDATE linkage SET movie_key='".$key."' WHERE movie_key='".$key."'; "; if($this->exec($sql)) return $key; return false; } function updateMovieAwards($code, $P) { foreach($P as $k=>$v){ if(!is_array($v)) $P[$k] = $this->sanitize(addslashes($v)); } $sql = 'UPDATE movie SET '; $sql .= "awards_en = '".$P['AWARDS_EN']."', "; $sql .= "awards_ja = '".$P['AWARDS_JA']."' "; $sql .= "WHERE code = '".(int)$code."'; "; return $this->exec($sql); } function insertMovie($P) { foreach($P as $k=>$v){ if(!is_array($v)) $P[$k] = $this->sanitize(addslashes($v)); } $key = md5($P['RELEASE_YEAR'].$P['TITLE_JA']); $sql = 'INSERT INTO movie (key, release_year, '; $sql .= ' title_en, title_ja, title_org, title_roman, '; $sql .= ' description_en, description_ja, '; $sql .= ' screen_type, screen_time, color, sound, rating, '; $sql .= ' public ) VALUES ('; $sql .= "'".$key."', "; $sql .= "'".$P['RELEASE_YEAR']."', "; $sql .= "'".$P['TITLE_EN']."', "; $sql .= "'".$P['TITLE_JA']."', "; $sql .= "'".$P['TITLE_ORG']."', "; $sql .= "'".$P['TITLE_ROMAN']."', "; $sql .= "'".$P['OUTLINE_EN']."', "; $sql .= "'".$P['OUTLINE_JA']."', "; $sql .= "'".$P['SCREEN_TYPE']."', "; $sql .= "'".$P['SCREEN_TIME']."', "; $sql .= "'".$P['COLOR']."', "; $sql .= "'".$P['SOUND']."', "; $sql .= "'".$P['RATING']."', "; $sql .= "'f' "; $sql .= ");"; if($this->exec($sql)) return $key; return false; } function getSuspendedMovies($limit=25, $offset=0) { $sql = "SELECT * FROM movie WHERE public = 'f' ORDER BY created_on DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql); $sql = "SELECT count(key) FROM movie WHERE public = 'f'; "; $total = $this->getOne($sql); return array($total, $rows); } function getOmnibusFromMovieKey($key) { $sql = "SELECT o.* FROM omnibus o LEFT JOIN linkage l ON o.code = l.code_omnibus "; $sql .= "WHERE movie_key = '".$key."';"; return $this->arrayFromSql($sql); } function insertOmnibus($P) { foreach($P as $k=>$v) $P[$k] = $this->sanitize(addslashes($v)); $sql = "SELECT nextval('omnibus_code_seq'::regclass);"; $code = $this->getOne($sql); $sql = "INSERT INTO omnibus (code, title_ja, title_en, type)"; $sql .= " VALUES ($code, '".$P['TITLE_JA']."', '".$P['TITLE_EN']."', '".$P['TYPE']."')"; if( $this->exec($sql) ) return $code; return false; } function updateOmnibus($code, $P) { foreach($P as $k=>$v) $P[$k] = $this->sanitize(addslashes($v)); $sql = 'UPDATE omnibus SET '; $sql .= "title_ja = '".$P['TITLE_JA']."', "; $sql .= "title_en = '".$P['TITLE_EN']."', "; $sql .= "type = '".$P['TYPE']."' "; $sql .= "WHERE code = '".(int)$code."';"; return $this->exec($sql); } function getOmnibusFromCode($code, $cache=USE_CACHE) { $sql = "SELECT * FROM omnibus WHERE code = '".$code."';"; return $this->arrayFromSql($sql); } function insertOmnibusLinkage($movie_key, $code_omnibus) { $sql = "INSERT INTO linkage (movie_key, code_omnibus) VALUES ('".$movie_key."', '".$code_omnibus."');"; return $this->exec($sql); } function getTitleFromOmnibusCode($code) { $sql = "SELECT m.* FROM movie m LEFT JOIN linkage l ON m.key = l.movie_key"; $sql .= " WHERE l.code_omnibus = '".$code."'"; $sql .= " ORDER BY priority ASC;"; return $this->arrayFromSql($sql); } function getPerson($limit=25, $page=1) { $offset = $limit * ($page-1); $sql = "SELECT count(code) FROM person;"; $total = $this->getOne($sql); $sql = "SELECT * FROM person ORDER BY code DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getPersonFromType($code_role, $limit=25, $page=1) { $offset = $limit * ($page-1); $sql = "SELECT count(p.code) FROM person p LEFT JOIN linkage l ON p.code = l.code_person WHERE l.code_role = '".$code_role."';"; $total = $this->getOne($sql); $sql = "SELECT p.* FROM person p LEFT JOIN linkage l ON p.code = l.code_person WHERE l.code_role = '".$code_role."' ORDER BY p.code DESC LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql, USE_CACHE); return array($total, $rows); } function getPersonFromCode($code) { $sql = "SELECT * FROM person WHERE code = '".(int)$code."';"; if($rows = $this->arrayFromSql($sql, NO_USE_CACHE)){ return $rows[0]; } return FALSE; } function getPersonFromAbsCode($code) { $sql = "SELECT * FROM person WHERE abs_code = '".$code."';"; return $this->arrayFromSql($sql, NO_USE_CACHE); } function getActorFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.memo_ja, l.memo_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Cast' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getStaffFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.memo_ja, l.memo_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Staff' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getProducerFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Producer' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getDirectorFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Director' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getProductionCompanyFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Production' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getDistributorFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Distributor' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function getProductionStudioFromMovieKey($movie_key) { $sql = "SELECT p.*, l.description_ja, l.description_en, l.priority FROM linkage l LEFT JOIN person p ON l.code_person = p.code "; $sql .= " WHERE l.movie_key = '".$movie_key."' AND l.code_role IN ( SELECT code FROM role WHERE type = 'Production_Studio' ) "; $sql .= " ORDER BY l.priority; "; return $this->arrayFromSql($sql, USE_CACHE); } function addAbsFromCode($code,$targetcode) { $person = $this -> getPersonFromCode($code); $person = $person[0]; $target = $this -> getPersonFromCode($targetcode); $target = $target[0]; if ($person['abs_code'] > 0) { $sql = 'UPDATE person SET abs_code = '. $person['abs_code']. ' WHERE code = '. $target['code']; $this->exec($sql); } elseif ($target['abs_code'] > 0) { $sql = 'UPDATE person SET abs_code = '. $target['abs_code']. ' WHERE code = '. $person['code']; $this->exec($sql); } else { $sql = "SELECT nextval('person_abs_code_seq'::regclass);"; $nextval = $this->getOne($sql); $sql = 'UPDATE person SET abs_code = ' . $nextval. ' WHERE code = '. $person['code']. ' OR code = '. $target['code']; $this->exec($sql); } } function delAbsFromCode($code,$targetcode) { $sql = 'UPDATE person SET abs_code = NULL where code = ' . $targetcode.';'; $this->exec($sql); } function updatePersonFromCode($code,$ja,$en) { $sql = "UPDATE person SET name_ja = '" .$ja. "' , name_en = '". $en. "' WHERE code = ".$code; $this->exec($sql); } function getRoleList() { $sql = "SELECT * FROM role ORDER BY code;"; return $this->arrayFromSql($sql, USE_CACHE); } function getRoleListWithCount() { $sql = "SELECT l.code_role, r.title_ja, r.title_en, count(l.code) FROM role r "; $sql .= " LEFT JOIN linkage l ON r.code = l.code_role "; $sql .= " WHERE r.type IN ('Director', 'Producer', 'Staff', 'Cast') "; $sql .= " GROUP BY l.code_role, r.title_ja, r.title_en "; $sql .= " ORDER BY count DESC limit 10 ; "; return $this->arrayFromSql($sql, USE_CACHE); } function getRoleType($roleArray) { foreach($roleArray as $r) $t[] = "'".$this->sanitize($r)."'"; $sql = "SELECT r.code, r.title_ja, r.title_en FROM role r "; $sql .= " WHERE r.active='t' AND r.title_ja IN (".join(",", $t).");"; return $this->arrayFromSql($sql, USE_CACHE); } function wordageSearchKeys($keys) { $q = mb_ereg_replace(" | |・", ' ', addslashes(trim($keys))); $tmp = explode(' ', $q); if(count($tmp)>=2){ foreach($tmp as $t){ if( mb_strlen($t)>1 ){ $p[] = $t; } } $q = ''; $q = join(' ', $p); } if( $lines = explode("\n", file_get_contents($_SERVER['DOCUMENT_ROOT'].'/lib/trans_kanji.txt')) ){ $src[0][] = '高'; $rpl[0][] = mb_convert_encoding("髙", "UTF-8", "HTML-ENTITIES"); $src[0][] = '�'; $rpl[0][] = '高'; foreach($lines as $l){ $kanji = explode("/", $l); $n = count($kanji); if($n>=2){ $m = 0; for($i=0;$i<$n;$i++){ for($j=0;$j<$n;$j++){ if($kanji[$i] == $kanji[$j]) continue; $src[$m][] = $kanji[$i]; $rpl[$m][] = $kanji[$j]; $src[$m][] = $kanji[$j]; $rpl[$m][] = $kanji[$i]; $m++; } } } } foreach($src as $index => $s){ $querys[md5(str_replace($s, $rpl[$index], $q))] = str_replace($s, $rpl[$index], $q); } }else{ $querys[] = $q; } return $querys; } function searchMovies($keys, $limit=25, $page=1, $sort='SCORE', $genre=null, $ryearfrom=null, $ryearto=null, $pyearfrom=null, $pyearto=null, $durationfrom=null, $durationto=null, $titleonly=false, $subtitle=null, $subtitle_other=False, $subtitle_text=null) { // global $gLang; $cri[] = "active = 't'"; if(strlen($keys)){ $keys = mb_ereg_replace("[  ]+", ' ', addslashes(trim($keys))); $q = str_replace("'", "\'", mb_ereg_replace(" | |・|・", ' ', trim($keys))); $keys = $this->sanitize($keys); if($titleonly==false){ $cri[] = "i.tsv @@ query"; }else{ if ( $gLang == 'en') { $cri[] = "to_tsvector('english', m.title_en) @@ query"; } else { $cri[] = "to_tsvector('japanese', m.title_ja) @@ query"; } } } if((int)$ryearfrom>0){ $cri[] = "m.release_year >= ".(int)$ryearfrom; } if((int)$ryearto>0){ $cri[] = "m.release_year <= ".(int)$ryearto; } if((int)$pyearfrom>0){ $cri[] = "m.production_year >= ".(int)$pyearfrom; } if((int)$pyearto>0){ $cri[] = "m.production_year <= ".(int)$pyearto; } if((int)$durationfrom>0){ $cri[] = "m.duration >= ".(int)$durationfrom; } if((int)$durationto>0){ $cri[] = "m.duration <= ".(int)$durationto; } if(strlen($subtitle)>0){ $cri[] = "m.subtitle && array['".join("','", $this->sanitize($subtitle))."']"; } if($subtitle_other){ $cri[] = "m.subtitle_text IS NOT NULL"; } if(strlen($subtitle_text)>0){ $cri[] = "m.subtitle_text ~* '". $this->sanitize($subtitle_text). "'"; } $where = 'WHERE ' . join(' AND ', $cri); if(is_array($genre)){ // $gwhere[] = "l.code_genre IN (".join(",", $genre).")"; $gwhere1 = $gwhere2 = $gwhere3 = array(); foreach($genre as $g){ // 34 // 5678 // ... switch ($g) { case "3": case "4": $gwhere1[] = "l1.code_genre = ".(int)$g; break; case "5": case "6": case "7": case "8": case "9": $gwhere2[] = "l2.code_genre = ".(int)$g; break; default: $gwhere3[] = "l3.code_genre = ".(int)$g; break; } } if($gwhere1) $gwhere[] = join(" OR ", $gwhere1); if($gwhere2) $gwhere[] = join(" OR ", $gwhere2); if($gwhere3) $gwhere[] = join(" OR ", $gwhere3); } // 並び替え $offset = $limit*($page-1); switch($sort){ case 'TITLE_A': $order_mode = 'm.title_roman ASC'; break; case 'TITLE_D': $order_mode = 'm.title_roman DESC'; break; case 'PROD_A': $order_mode = 'm.production_year ASC'; break; case 'PROD_D': $order_mode = 'm.production_year DESC'; break; case 'RELE_A': $order_mode = 'm.release_year ASC'; break; case 'RELE_D': $order_mode = 'm.release_year DESC'; break; case 'SCORE': default: $order_mode = 'rank DESC'; } $opt = sprintf(" ORDER BY %s LIMIT %d OFFSET %d ", $order_mode, (int)$limit, (int)$offset); // SQL作成 $query_base ="SELECT %s FROM %s ". "LEFT JOIN search_index_movie_fulltext i ON m.key = i.movie_key, ". "to_tsquery('japanese', web_query('%s')) as query %s %s;"; $select_fetch = "m.*, (ts_rank_cd(to_tsvector('japanese', m.title_ja), query)*10 + ts_rank_cd(to_tsvector('japanese', m.title_en), query)*10 + ts_rank_cd( i.tsv, query)) as rank "; $select_count = "count(*)"; $target = "movie m"; if ($gwhere) { $target = "(SELECT * FROM movie WHERE code IN (SELECT movie.code FROM movie " . "LEFT JOIN linkage l1 ON l1.movie_key = movie.key AND l1.code_genre IN (3,4) ". "LEFT JOIN linkage l2 ON l2.movie_key = movie.key AND l2.code_genre IN (5,6,7,8,9) ". "LEFT JOIN linkage l3 ON l3.movie_key = movie.key AND l3.code_genre NOT IN (3,4,5,6,7,8,9) ". "WHERE (" . join(") AND (", $gwhere). "))) as m"; } $total = $this->getOne(sprintf($query_base, $select_count, $target, $keys, $where, '')); $rows = $this->arrayFromSql(sprintf($query_base, $select_fetch, $target, $keys, $where, $opt)); return array($total, $rows); if(strlen($keys)){ if( $lines = explode("\n", file_get_contents($_SERVER['DOCUMENT_ROOT'].'/lib/trans_kanji.txt')) ){ foreach($lines as $l){ $kanji = explode("/", $l); $n = count($kanji); if($n>=2){ $m = 0; for($i=0;$i<$n;$i++){ for($j=0;$j<$n;$j++){ if($kanji[$i] == $kanji[$j]) continue; $src[$m][] = $kanji[$i]; $rpl[$m][] = $kanji[$j]; $src[$m][] = $kanji[$j]; $rpl[$m][] = $kanji[$i]; $m++; } } } } foreach($src as $index => $s){ $qq[md5(str_replace($s, $rpl[$index], $q))] = str_replace($s, $rpl[$index], $q); } foreach($qq as $query){ $f1[] = "s.fulltext @@ '*D+ ".$query."'"; } } } } function searchPerson($keys, $limit=250, $page=1, $role=null, $sort_key='t1.name_en') { $offset = $limit*($page-1); $queries = $this->wordageSearchKeys($keys); $searchTargets = array('name_ja', 'name_yomi_full', 'name_en'); $roleTitles = array('title_ja', 'title_en'); $query_base = " SELECT p.*, 0 as rank FROM person p LEFT JOIN person p2 ON %s.code = %s.abs_code". " LEFT JOIN linkage l ON l.code_person = p.code ". " LEFT JOIN role r ON r.code = l.code_role ". " LEFT JOIN movie m ON m.key = l.movie_key ". "WHERE p.active = 't' AND l.movie_key IS NOT NULL AND m.active = 't'"; if($keys) $query_base .= " AND to_tsvector('japanese', %s.%s) @@ to_tsquery('japanese', web_query('%s'))"; if($role){ $role_sql = "SELECT title_en FROM role WHERE title_ja IN ('". join("','", $role) ."')"; $role_rows = $this->arrayFromSql($role_sql); if($role_rows){ $role = array();foreach($role_rows as $rr) $role[] = $rr['title_en']; } $query_base .= " AND r.title_en IN ('". join("','", $role) ."')"; } foreach($queries as $q){ $q = $this->sanitize($q); foreach($searchTargets as $st){ foreach($roleTitles as $t){ $p_sql[] = sprintf($query_base, 'p', 'p2', 'p', $st, $q, $t); $p_sql[] = sprintf($query_base, 'p', 'p2', 'p2', $st, $q, $t); $p_sql[] = sprintf($query_base, 'p2', 'p', 'p', $st, $q, $t); $p_sql[] = sprintf($query_base, 'p2', 'p', 'p2', $st, $q, $t); } } } $union_sql = '('. join(") UNION ALL (", $p_sql). ') '; $total_sql = "SELECT count(t1.code) FROM (SELECT DISTINCT ON (u.code) u.code FROM ( ".$union_sql.") as u) as t1;"; $total = $this->getOne($total_sql); $row_sql = "SELECT t1.* FROM ( SELECT DISTINCT ON (u.code) u.* FROM ( ".$union_sql.") as u ) as t1 ORDER BY t1.rank DESC, t1.name_yomi_full LIMIT ".(int)$limit ."OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($row_sql); return array($total, $rows); } function searchContact($keys, $limit=25, $page=1, $type=null, $domestic='f') { $offset = $limit*($page-1); $q = mb_ereg_replace(" | |・|・", ' ', addslashes(trim($keys))); $sql = "SELECT * FROM contact WHERE active = 't' AND domestic = '".$this->sanitize($domestic)."' AND type!='MOVIE' "; if(strlen($keys)>0) $sql .= " AND to_tsvector('japanese', name) @@ to_tsquery('japanese', web_query('".$q."')) "; if(is_array($type)){ foreach($type as $t) $typeArray[] = "'".$this->sanitize($t)."'"; $sql .= " AND type IN (".join(",", $typeArray).") "; }else if(strlen($type)){ $sql .= " AND type = '".$this->sanitize($type)."' "; } $sql .=" LIMIT ".(int)$limit. " OFFSET ". (int)$offset."; "; $rows = $this->arrayFromSql($sql); $sql = "SELECT count(code) FROM contact WHERE active='t' AND domestic = '".$this->sanitize($domestic)."' AND type!='MOVIE' "; if(strlen($keys)>0) $sql .= " AND to_tsvector('japanese', name) @@ to_tsquery('japanese', web_query('".$q."')) "; if(is_array($type)){ foreach($type as $t) $typeArray[] = "'".$this->sanitize($t)."'"; $sql .= " AND type IN (".join(",", $typeArray).") "; }else if(strlen($type)){ $sql .= " AND type = '".$this->sanitize($type)."' "; } $sql .= ";"; $total = $this->getOne($sql); return array($total, $rows); } function getTrailerFromMovieKey($key) { $sql = "SELECT * FROM trailer WHERE movie_key = '".$key."' AND path != '' AND active = 't' AND public = 't' ; "; return $this->arrayFromSql($sql, USE_CACHE); } function getTrailerFromCode($codeTrailer) { $sql = "SELECT * FROM trailer WHERE code = '". (int)$codeTrailer."' AND path != '' AND active = 't' AND public = 't' ; "; if($rows = $this->arrayFromSql($sql, USE_CACHE)){ return $rows[0]; } return false; } function userFromCode($code) { if((int)$code<=0) return false; $sql = "SELECT * FROM users WHERE code = '".(int)$code."';"; $rows = $this->arrayFromSql($sql); if(!$rows) return false; return $rows[0]; } function getUserList() { $sql = "SELECT * FROM users ORDER BY created_on DESC;"; return $this->arrayFromSql($sql); } function getIndustryType() { $sql = "SELECT * FROM industry_type WHERE active='t' AND priority > 0 ORDER BY priority ASC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getOccupationType() { $sql = "SELECT * FROM occupation_type WHERE active='t' AND priority > 0 ORDER BY priority ASC; "; return $this->arrayFromSql($sql, USE_CACHE); } function getUserProfileFromCode($codeUsers) { $sql .= "SELECT u.code, u.opt_in, u.email, up.*, uo.*, uo.name as org_name, uo.dept as dept_name, uo.zip_code as zipcode FROM users u LEFT JOIN users_profile up ON u.code = up.code_users "; $sql .= " LEFT JOIN users_organization_info uo ON u.code = uo.code_users WHERE u.code = '".$codeUsers."';"; return ($rows = $this->arrayFromSql($sql) ) ? $rows[0] : FALSE; } } ?>