function getRoleNameFromType($type)
{
$sql = "SELECT code, title_ja, title_en FROM role WHERE type='".$type."' ORDER BY code;";
return $this->arrayFromSql($sql, USE_CACHE);
}
function quituser($user)
{
$sql = "UPDATE users SET email = '***".$user['email']."***".date("Y-m-d H:i:s")."***', active = 'f', last_modified = now() WHERE code = '".(int)$user['code']."';";
$sql .= "DELETE FROM cookie WHERE code_user = '".(int)$user['code']."'; ";
return $this->exec($sql);
}
function resetPassword($codeUser)
{
$chars = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjkmnopqrstuvwxyz23456789';
$max = strlen($chars);
$pw = '';
mt_srand ((float)microtime() * 1000000);
for($i=0;$i<8;$i++){
$pw .= $chars[mt_rand(0, $max-1)];
}
$encoded_pw = $this->encoding_pw($pw);
$sql = "UPDATE users SET pw = '".$encoded_pw."', last_modified = now() WHERE code = '".(int)$codeUser."';";
if($this->exec($sql)){
return $pw;
}else{
return false;
}
}
function checkUserFromCookie($cookie)
{
if(isset($cookie[COOKIE_AUTH]) && ($cookie[COOKIE_AUTH] != '')){
$user = $this->userFromCookieToken($cookie[COOKIE_AUTH]);
// ログインしてない
if($user == false) return false;
// アクティベートしてない
if(strlen($user['scrambled'])) return false;
// パスワードが変更されているじゃないか！
if($this->encoding_auth_token($user['pw'], $user['code']) != $cookie[COOKIE_AUTH]) return false;
// OKなので users.codeを返す
return $user['code'];
}
return false;
}
function userFromCookieToken($cookie)
{
$sql = "SELECT u.* FROM users u LEFT JOIN cookie c ON u.code = c.code_user ";
$sql .= "WHERE c.token = '".$cookie."'; ";
$rows = $this->arrayFromSql($sql);
if(count($rows)==0) return false;
return $rows[0];
}
function cookieFromToken($token)
{
$sql = "SELECT * FROM cookie WHERE token = '". addslashes($token). "'; ";
return $this->arrayFromSql($sql, NOT_USE_CACHE);
}
function deleteCookieFromCode($code_user)
{
$sql = "DELETE FROM cookie WHERE code_user = ". (int)$code_user. "; ";
return $this->exec($sql);
}
function setTokenToCookieFromUserCode($code_user, $token)
{
if($this->deleteCookieFromCode($code_user)){
$sql = "INSERT INTO cookie (code_user, token, ipaddr) VALUES ('". addslashes($code_user). "', '". addslashes($token). "', '". $_SERVER['REMOTE_ADDR']. "'); ";
return $this->exec($sql);
}
return false;
}
function scrambled()
{
$chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
$max = strlen($chars);
$pw = '';
mt_srand ((float)microtime() * 1000000);
for($i=0;$i<24;$i++){
$pw .= $chars[mt_rand(0, $max-1)];
}
return $pw;
}
function encoding_pw($pw)
{
return md5($pw.'jpitch-unijapan');
}
function encoding_auth_token($pw,$code)
{
return md5($pw.$code.$_SERVER['HTTP_HOST']);
}
/*
function registUserAccount($P, $acount_level=ACCOUNT_LEVEL_PUBLIC)
{
$P = $this->sanitize($P);
foreach($P as $k=>$v) $P[$k] = trim($v);
$scrambled = $this->scrambled();
$pw = $this->encoding_pw($P['PW']);
$sql = "INSERT INTO users (email, pw, handle, account_level, country, scrambled) VALUES ".
"('".$P['EMAIL']."', '".$pw."', '".$P['HANDLE']."', '".$acount_level."', '".$P['COUNTRY']."', '".$scrambled."' );";
if($this->exec($sql)) return $scrambled;
return false;
}
*/
function registerUserAccount($P, $account_level=ACCOUNT_LEVEL_PUBLIC)
{
global $gLang;
$P = $this->sanitize($P);
$scrambled = $this->scrambled();
$pw = $this->encoding_pw($P['PW']);
$sql = "SELECT nextval('users_code_seq');";
$code = $this->getOne($sql);
$handle = 'UJID'.str_pad($code, 6, '0', STR_PAD_LEFT);
$name = $P['LAST_NAME'].' '.($P['MIDDLE_NAME']?" ".$P['MIDDLE_NAME']:'').$P['FIRST_NAME'];
// country は users_profile へ、company は users_organization_info へ
$sql = "INSERT INTO users (code, email, pw, handle, name, company, account_level, country, scrambled, opt_in, lang) VALUES ".
"('".$code."', '".$P['EMAIL']."', '".$pw."', '".$handle."', '".$name."', '".$P['ORG_NAME']."', '".$account_level."', '".$P['COUNTRY']."', '".$scrambled."', '".($P['OPT_IN']?TRUE:FALSE)."', '".$gLang."' );";
if($this->exec($sql)) return array($code, $scrambled);
return false;
}
function insertUserProfile($codeUsers, $P)
{
// $this->registerUserAccount で既にサニタイズされてるはず
// 個別に呼び出すときはサニタイズして渡すこと
/* birth_year, birth_month, birth_day, */
/* '".$P['BIRTH_YEAR']."', '".$P['BIRTH_MONTH']."', '".$P['BIRTH_DAY']."',*/
$sql = "INSERT INTO users_profile ( code_users, type,
first_name, last_name, middle_name,
first_name_kana, last_name_kana, middle_name_kana,
sex,
country,
industry, occupation,
purpose, reference
) VALUES (
'".(int)$codeUsers."',
'".$P['USER_TYPE']."',
'".$P['FIRST_NAME']."', '".$P['LAST_NAME']."', '".$P['MIDDLE_NAME']."',
'".$P['FIRST_NAME_KANA']."', '".$P['LAST_NAME_KANA']."', '".$P['MIDDLE_NAME_KANA']."',
'".$P['SEX']."',
'".$P['COUNTRY']."',
'".$P['INDUSTRY']."', '".$P['OCCUPATION']."',
'".$P['PURPOSE']."', '".$P['REFERENCE']."'
);";
return $this->exec($sql);
}
function insertUserOrganizationInfo($codeUsers, $P)
{
// $this->registerxphpUserAccount で既にサニタイズされてるはず
// 個別に呼び出すときはサニタイズして渡すこと
$sql = "INSERT INTO users_organization_info ( code_users,
name, dept,
zip_code, address,
tel, fax, url
) VALUES (
'".(int)$codeUsers."',
'".$P['ORG_NAME']."', '".$P['DEPT_NAME']."',
'".$P['ZIPCODE']."', '".$P['ADDRESS']."',
'".$P['TEL']."', '".$P['FAX']."', '".$P['URL']."'
);";
return $this->exec($sql);
}
function registerUser($P, $account_level=ACCOUNT_LEVEL_PUBLIC)
{
$res1 = $res2 = TRUE;
$this->begin();
list($code, $scrambled) = $this->registerUserAccount($P, $account_level);
if($code>0){
$res1 = $this->insertUserProfile($code, $P);
if($P['USER_TYPE'][0]!='1'){
$res2 = $this->insertUserOrganizationInfo($code, $P);
}
if($res1==TRUE && $res2==TRUE){
$this->commit();
return $scrambled;
}
}
// 登録失敗
$this->abort();
return FALSE;
}
function getUserFromEmail($email, $cache=USE_CACHE)
{
$sql = "SELECT * FROM users WHERE email = '".$this->sanitize($email)."' AND scrambled IS NULL AND active = 't';";
$rows = $this->arrayFromSql($sql, $cache);
if(count($rows)!=1) return false;
return $rows[0];
}
function getUserFromCode($code, $cache=USE_CACHE)
{
$sql = "SELECT * FROM users WHERE code = '".$this->sanitize($code)."' AND scrambled IS NULL AND active = 't';";
$rows = $this->arrayFromSql($sql, $cache);
if(count($rows)!=1) return false;
return $rows[0];
}
function getUserFromScrambled($scrambled)
{
if(!strlen($scrambled)) return false;
$sql = "SELECT * FROM users WHERE scrambled = '".$scrambled."';";
$rows = $this->arrayFromSql($sql, NOT_USE_CACHE);
if(count($rows)!=1) return false;
return $rows[0];
}
function activateUserAccountFromCode($code)
{
$sql = "UPDATE users SET scrambled = NULL, last_modified = now() WHERE code = '".$code."';";
return $this->exec($sql);
}
function getCountryCode()
{
$sql = "SELECT name_en, name_ja, num, alpha2 FROM country ORDER BY name_en;";
return $this->arrayFromSql($sql, USE_CACHE);
}
function updateLastAccess($codeUsers)
{
if((int)$codeUsers<=0) return false;
$sql = "UPDATE users SET last_access = now() WHERE code = '".(int)$codeUsers."';";
return $this->exec($sql);
}
function updateUserAccount($P, $account_level=ACCOUNT_LEVEL_PUBLIC)
{
global $gLang, $gUser;
$P = $this->sanitize($P);
$scrambled = $this->scrambled();
$pw = $this->encoding_pw($P['PW']);
$name = $P['LAST_NAME'].' '.($P['MIDDLE_NAME']?" ".$P['MIDDLE_NAME']:'').$P['FIRST_NAME'];
// country は users_profile へ、company は users_organization_info へ
$sql = "UPDATE users SET ";
if($P['EMAIL']) $sql .= "email = '".$P['EMAIL']."', ";
if($P['PW']) $sql .= "pw = '".$pw."', ";
$sql .= "name = '".$name."', ";
$sql .= "company = '".$P['ORG_NAME']."', ";
$sql .= "country = '".$P['COUNTRY']."', ";
// $sql .= "scrambled = '".$scrambled."', ";
$sql .= "opt_in = '".(($P['OPT_IN']=='1')?'t':'f')."'";
$sql .= " WHERE code = '".$gUser['code']."';";
if($this->exec($sql)) return array($gUser['code'], $scrambled);
return false;
}
function updatetUserProfile($codeUsers, $P)
{
// $this->registerUserAccount で既にサニタイズされてるはず
// 個別に呼び出すときはサニタイズして渡すこと
/*
birth_year = '".$P['BIRTH_YEAR']."',
birth_month = '".$P['BIRTH_MONTH']."',
birth_day = '".$P['BIRTH_DAY']."',
*/
$sql = "UPDATE users_profile SET
type = '".$P['USER_TYPE']."',
first_name = '".$P['FIRST_NAME']."',
last_name = '".$P['LAST_NAME']."',
middle_name = '".$P['MIDDLE_NAME']."',
first_name_kana = '".$P['FIRST_NAME_KANA']."',
last_name_kana = '".$P['LAST_NAME_KANA']."',
middle_name_kana = '".$P['MIDDLE_NAME_KANA']."',
sex = '".$P['SEX']."',
country = '".$P['COUNTRY']."',
industry = '".$P['INDUSTRY']."',
occupation = '".$P['OCCUPATION']."'
WHERE code_users = '".$codeUsers."';";
return $this->exec($sql);
}
function updateUserOrganizationInfo($codeUsers, $P)
{
// $this->registerUserAccount で既にサニタイズされてるはず
// 個別に呼び出すときはサニタイズして渡すこと
$sql = "UPDATE users_organization_info SET
name = '".$P['ORG_NAME']."',
dept = '".$P['DEPT_NAME']."',
zip_code = '".$P['ZIPCODE']."',
address = '".$P['ADDRESS']."',
tel = '".$P['TEL']."',
fax = '".$P['FAX']."',
url = '".$P['URL']."'
WHERE code_users = '".$codeUsers."';";
return $this->exec($sql);
}
function updateUser($P, $account_level=ACCOUNT_LEVEL_PUBLIC)
{
global $gUser;
if((int)$gUser['code']<=0) return FALSE;
$res1 = $res2 = TRUE;
$this->begin();
list($code, $scrambled) = $this->updateUserAccount($P, $account_level);
if($code>0){
$res1 = $this->updatetUserProfile($gUser['code'], $P);
$res2 = $this->updaTEUserOrganizationInfo($gUser['code'], $P);
if($res1==TRUE && $res2==TRUE){
$this->commit();
return $scrambled;
}
}
// 登録失敗
$this->abort();
return FALSE;
}
function emailDuplicateCheck($email)
{
$sql = "SELECT count(code) FROM users WHERE email = '".$this->sanitize($email)."'; ";
return ($n = $this->getOne($sql)) ? TRUE : FALSE;
}/*
function getCompanyType()
{
$sql = "SELECT type, count(code) FROM company WHERE type!='MOVIE' AND domestic='$domestic' GROUP BY type ORDER BY count DESC;";
return $this->arrayFromSql($sql);
}
*/
function getCompanyFromCode($codeCompany)
{
$sql = "SELECT * FROM company WHERE active = 't' AND code = '".(int)$codeCompany."'; ";
return ( $rows = $this->arrayFromSql($sql, USE_CACHE) ) ? $rows[0] : false;
}
function getCompany($npp, $page)
{
$offset = $npp * ($page-1);
$sql = "SELECT count(code) FROM company WHERE active = 't' AND as_contact = 't';";
$total = $this->getOne($sql);
$sql = "SELECT * FROM company WHERE active = 't' AND as_contact = 't' ";
$sql .= " ORDER BY name_yomi ASC ";
$sql .= " LIMIT ".(int)$npp ." OFFSET ".(int)$offset."; ";
$rows = $this->arrayFromSql($sql, USE_CACHE);
return array($total, $rows);
}
function searchCompany($keys, $npp, $page, $type)
{
$offset = $npp * ($page-1);
// $kw = explode(' ', mb_ereg_replace("[ ]+", ' ', addslashes(trim($keys))));
if(is_array($type)){
foreach($type as $t){
if($t=='PRODUCTION') $as_query[] = " as_production = 't' ";
if($t=='DISTRIBUTORS') $as_query[] = " as_distributor = 't' ";
if($t=='COMMISSIONS') $as_query[] = " as_commission = 't' ";
if($t=='ASSOCIATIONS') $as_query[] = " as_association = 't' ";
if($t=='ANIMATION') $as_query[] = " as_animation = 't' ";
if($t=='FESTIVALS') $as_query[] = " as_festival = 't' ";
if($t=='SCHOOLS') $as_query[] = " as_school = 't' ";
if($t=='EQUIPMENT') $as_query[] = " as_equipment = 't' ";
if($t=='FUNDS') $as_query[] = " as_fund = 't' ";
if($t=='SALES') $as_query[] = " as_sales = 't' ";
if($t=='CASTING') $as_query[] = " as_casting = 't' ";
if($t=='SUBTITLE') $as_query[] = " as_subtitle = 't' ";
if($t=='TRANSLATION') $as_query[] = " as_translation = 't' ";
}
}
if($keys){
$querys = $this->wordageSearchKeys($keys);
$searchTargets = array('name_ja', 'name_en', 'name_yomi');
foreach($querys as $q){
foreach($searchTargets as $st){
$c_sql[] = "( SELECT c.* FROM company c WHERE active = 't' AND as_contact != 't' AND c.".$st." @@ '*D+ ".$q."' ".(($as_query)?(" AND (".join(" OR ", $as_query).')'):'').")";
$c_sql[] = "( SELECT c.* FROM company c WHERE active = 't' AND as_contact != 't' AND c.".$st." ~* '".$q."' ".(($as_query)?(" AND (".join(" OR ", $as_query).')'):'').")";
}
}
}else{
$c_sql[] = "( SELECT c.* FROM company c WHERE c.active = 't' AND c.as_contact != 't' ".(($as_query)?(" AND (".join(" OR ", $as_query).') '):'').")";
}
$union_sql = join(" UNION ALL ", $c_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.name_en LIMIT ".(int)$npp ."OFFSET ". (int)$offset."; ";
$rows = $this->arrayFromSql($row_sql);
return array($total, $rows);
/*
$sql = "SELECT count(code) FROM company WHERE active = 't' AND as_contact != 't'";
$sql .= ($key_query) ? ' AND '. $key_query : '';
$sql .= ($query) ? " AND (".join(" OR ", $query).") " : '';
$sql .= ";";
$total = $this->getOne($sql);
$sql = "SELECT * FROM company WHERE active = 't' AND as_contact != 't' ";
$sql .= ($query) ? " AND (".join(" OR ", $query).") " : '';
$sql .= ($key_query) ? ' AND '. $key_query : '';
$sql .= " ORDER BY name_yomi ASC ";
$sql .= " LIMIT ".(int)$npp ." OFFSET ".(int)$offset."; ";
$rows = $this->arrayFromSql($sql, USE_CACHE);
return array($total, $rows);
*/
}
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;
}
}
?>