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;
}
}
?>