summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--data/docs/1620
-rw-r--r--data/docs/index1
-rw-r--r--data/global.pl1
-rw-r--r--data/lang.txt203
-rw-r--r--data/script.js196
-rw-r--r--data/style.css30
-rw-r--r--lib/VNDB/DB/Chars.pm16
-rw-r--r--lib/VNDB/DB/Misc.pm13
-rw-r--r--lib/VNDB/DB/Staff.pm141
-rw-r--r--lib/VNDB/DB/VN.pm18
-rw-r--r--lib/VNDB/Handler/Chars.pm16
-rw-r--r--lib/VNDB/Handler/Misc.pm3
-rw-r--r--lib/VNDB/Handler/Staff.pm412
-rw-r--r--lib/VNDB/Handler/VNPage.pm61
-rw-r--r--lib/VNDB/Util/CommonHTML.pm9
-rw-r--r--lib/VNDB/Util/LayoutHTML.pm2
-rwxr-xr-xutil/jsgen.pl1
-rw-r--r--util/sql/func.sql54
-rw-r--r--util/sql/staff.sql59
19 files changed, 1230 insertions, 26 deletions
diff --git a/data/docs/16 b/data/docs/16
new file mode 100644
index 00000000..97d7b2db
--- /dev/null
+++ b/data/docs/16
@@ -0,0 +1,20 @@
+:TITLE:Adding/Editing Staff Members
+:INC:index
+
+:SUB:Introduction
+<p>
+ Add major staff members.
+</p>
+
+:SUB:General info
+<dl>
+ <dt>Name (romaji)</dt><dd>Person true name, in the latin alphabet.
+ Check out the <a href="/d5">general editing guidelines</a>
+ for information on name order and romanization. Try to use the full name when known.
+ <a href="/d5.1">Romanise</a> according to our guidelines.
+ </dd><dt>Original name</dt><dd>The actual name of the person, in the case it is not officially in the latin alphabet. Leave empty if it's the same as the "Name (romaji)". If possible, try to include a space between the given name and the surname.
+ </dd><dt>Staff note</dt><dd>Comment.
+ </dd><dt>Gender</dt><dd>.
+ </dd><dt>Primary language</dt><dd>.
+ </dd>
+</dl>
diff --git a/data/docs/index b/data/docs/index
index e0d11a8b..a1ca1acd 100644
--- a/data/docs/index
+++ b/data/docs/index
@@ -5,6 +5,7 @@
<li><a href="/d15">Special Games</a></li>
<li><a href="/d3">Releases</a></li>
<li><a href="/d4">Producers</a></li>
+ <li><a href="/d16">Staff</a></li>
<li><a href="/d12">Characters</a></li>
<li><a href="/d10">Tags &amp; traits</a></li>
<li><a href="/d13">Capturing screenshots</a></li>
diff --git a/data/global.pl b/data/global.pl
index cf9a9fd4..b07281b2 100644
--- a/data/global.pl
+++ b/data/global.pl
@@ -113,6 +113,7 @@ our %S = (%S,
changes => [ 25, 'VNDB Recent Changes', '/hist' ],
posts => [ 25, 'VNDB Recent Posts', '/t' ],
},
+ staff_roles => [qw|script chardesign music director art songs staff|],
);
diff --git a/data/lang.txt b/data/lang.txt
index 48365acf..d64c8e92 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -3053,6 +3053,9 @@ tr : Yapımcılar
uk : Компанії
it : Editori
+:_menu_staff
+en : Staff
+
:_menu_characters
en : Characters
ru : Персонажи
@@ -3260,6 +3263,9 @@ tr : Yapımcı ekle
uk : Додати компанію
it : Aggiungi Editore
+:_menu_addstaff
+en : Add Staff
+
:_menu_addcharacter
en : Add Character
ru : Добавить персонажа
@@ -3884,6 +3890,9 @@ tr : Yapımcılar
uk : Компанії
it : Editori
+:_searchbox_staff
+en : Staff
+
:_searchbox_chars
en : Characters
ru : Персонажи
@@ -5328,8 +5337,186 @@ tr : Sonuç bulunamadı
uk : Нічого не знайдено
it : Nessun risultato
+#############################################################################
+## Staff pages (/s/*) ##
+#############################################################################
+# Handler::Staff
+
+# Built-in credits
+
+:_credit_script
+en : Script writer
+
+:_credit_chardesign
+en : Character design
+
+:_credit_music
+en : Composer
+
+:_credit_director
+en : Director
+
+:_credit_art
+en : Artist
+
+:_credit_songs
+en : Vocals
+
+:_credit_voice
+en : Additional voices
+
+:_credit_staff
+en : Staff
+
+# Staff diff fields (/s+.+)
+
+:_revfield_s_name
+en : Name (romaji)
+
+:_revfield_s_original
+en : Original name
+
+:_revfield_s_gender
+en : Gender
+
+:_revfield_s_lang
+en : Language
+
+:_revfield_s_aliases
+en : Aliases
+
+:_revfield_s_l_wp
+en : Wikipedia link
+
+:_revfield_s_desc
+en : Description
+
+# Staff browser
+
+:_sbrowse_title
+en : Browse staff
+
+:_sbrowse_list
+en : Staff list
+:_sbrowse_searchres
+en : Search results
+
+:_sbrowse_noresults
+en : No results found
+:_staff_credits
+en : Credits
+
+:_staff_as
+en : as
+
+:_staff_gender
+en : Gender
+
+:_staff_language
+en : Language
+
+:_staff_aliases
+en : Aliases
+
+:_staff_l_wp
+en : Wikipedia
+
+:_staff_bio
+en : Info
+
+:_staff_col_title
+en : Title
+
+:_staff_col_released
+en : Released
+
+:_staff_col_role
+en : Role
+
+:_staff_col_credit
+en : Credit
+
+:_staff_col_note
+en : Note
+
+# Add/edit staff
+
+:_staffe_title_edit
+en : Edit [_1]
+
+:_staffe_title_add
+en : Add staff member
+
+:_staffe_form_generalinfo
+en : General info
+
+:_staffe_form_name
+en : Name (romaji)
+
+:_staffe_form_original
+en : Original name
+
+:_staffe_form_alias
+en : Alias (romaji)
+
+:_staffe_form_original_alias
+en : Original alias
+
+:_staffe_form_original_note
+en : The original name of the person, leave blank if it is already in the Latin alphabet.
+
+:_staffe_form_note
+en : Staff note
+
+:_staffe_form_gender
+en : Gender
+
+:_staffe_form_lang
+en : Primary language
+
+:_staffe_form_wikipedia
+en : Wikipedia link
+
+:_staffe_aliases
+en : Aliases
+
+:_staffe_aliases_sel
+en : Selected aliases
+
+:_staffe_aliases_add
+en : Add alias
+
+:_staffe_vns
+en : Visual novels
+
+:_sedit_form_alias_none
+en : None
+
+:_vnstaff_edit_title
+en : Edit staff of [_1]
+
+:_vnstaff_edit_credits
+en : Credits
+
+:_vnstaffe_form_staff
+en : Staff
+
+:_vnstaffe_form_role
+en : Credit
+
+:_vnstaffe_form_note
+en : Note
+
+:_vnstaffe_add
+en : Add staff
+
+:_vnstaffe_none
+en : None
+
+:_vnstaffe_credit_exists
+en : Credit already given.
#############################################################################
## Release pages (/r/*) ##
@@ -8673,6 +8860,9 @@ tr : Diğer tüm yayımlar
uk : Усі інші релізи
it : Tutte le altre pubblicazioni
+:_charp_voice
+en : Voiced by
+
:_charp_description
en : Description
ru : Описание
@@ -14294,6 +14484,9 @@ tr : karakterler
uk : персонажі
it : personaggi
+:_vnpage_tab_staff
+en : staff
+
:_vnpage_rel
en : Releases
ru : Выпуски
@@ -14405,6 +14598,12 @@ tr : karakter ekle
uk : додати персонажа
it : aggiungi personaggio
+:_vnpage_staff_add
+en : add staff
+
+:_vnpage_staff_edit
+en : edit staff
+
:_vnpage_rel_add
en : add release
ru : добавить выпуск
@@ -14793,7 +14992,7 @@ uk : Огляньте [url,_1,історію редагувань], можлив
it : Controlla le [url,_1,attività] per eventuali modifiche recenti riguardo a ciò che vuoi cambiare.
:_editmsg_msg_search
-en : [url,_1,Search the database] to see if we already have information about this [index,_2,visual novel,release,producer,character].
+en : [url,_1,Search the database] to see if we already have information about this [index,_2,visual novel,release,producer,character,person].
ru : [url,_1,Воспользуйтесь поиском], ведь вполне возможно, что у нас уже есть информация об [index,_2,этой новелле,этом выпуске,этой компании,этом персонаже].
cs : [url,_1,Prohledejte databázi], zda již nemáme informaci o [index,_2,této vizuální novele,tomto vydání,tomto producentovi,této postavě].
hu : [url,_1,Nézd át az adatbázist], hogy van-e már információ erről a [index,_2,visual novelről, kiadásról, készítőről, szereplőről].
@@ -14817,7 +15016,7 @@ uk : Відновлення
it : Ripristina
:_editmsg_revert_msg
-en : You are editing an old revision of this [index,_1,visual novel,release,producer,character].
+en : You are editing an old revision of this [index,_1,visual novel,release,producer,character,person].
If you save it, all changes made after this revision will be reverted!
ru : Вы правите старую редакцию страницы [index,_1,новеллы,выпуска,компании,персонажа].
Если вы сохраните её, все изменения, сделанные после этой правки, будут утеряны!
diff --git a/data/script.js b/data/script.js
index ce66bc02..6a3bd25e 100644
--- a/data/script.js
+++ b/data/script.js
@@ -16,6 +16,8 @@
* tgl -> VN tag linking
* tvs -> VN page tag spoilers
* vnr -> VN relation editor
+ * vns -> VN staff
+ * sal -> Staff aliases editor
*/
/* Internationalization note:
@@ -2047,6 +2049,200 @@ if(byId('jt_box_chare_vns'))
+/* S T A F F (/s+/edit) */
+
+function salLoad () {
+ var aliases = byId('aliases').value.split('|||');
+ for (var i = 0; i < aliases.length && aliases[i].length > 1; i++) {
+ var al = aliases[i].split(',', 3);
+ salAdd(al[0], al[1], al[2]);
+ }
+ salEmpty();
+
+ // bind the add-link
+ byName(byClass(byId('alias_new'), 'td', 'tc_add')[0], 'a')[0].onclick = salFormAdd;
+ byName(byId('maincontent'), 'form')[0].onsubmit = salSerialize;
+}
+
+function salAdd(aid, name, original) {
+ byId('alias_tbl').appendChild(tag('tr', {id:'alias_tr_'+aid},
+ tag('td', {'class':'tc_name' }, tag('input', {type:'text', 'class':'text', value:name})),
+ tag('td', {'class':'tc_original' }, tag('input', {type:'text', 'class':'text', value:original})),
+ tag('td', {'class':'tc_add' },
+ tag('input', {type:'hidden', value:aid}),
+ tag('a', {href:'#', onclick:salDel}, mt('_js_remove')))
+ ));
+
+ salEmpty();
+}
+
+function salEmpty() {
+ var tbl = byId('alias_tbl');
+ if (byName(tbl, 'tr').length < 1)
+ tbl.appendChild(tag('tr', {id:'alias_tr_none'}, tag('td', {colspan:3}, mt('_sedit_form_alias_none'))));
+ else if (byId('alias_tr_none'))
+ tbl.removeChild(byId('alias_tr_none'));
+}
+
+function salSerialize() {
+ var tbl = byName(byId('alias_tbl'), 'tr');
+ var a = [];
+ for (var i = 0; i < tbl.length; ++i) {
+ if(tbl[i].id == 'alias_tr_none')
+ continue;
+ var name = byName(byClass(tbl[i], 'td', 'tc_name')[0], 'input')[0].value;
+ var orig = byName(byClass(tbl[i], 'td', 'tc_original')[0], 'input')[0].value;
+ var id = byName(byClass(tbl[i], 'td', 'tc_add')[0], 'input')[0].value;
+ a[a.length] = [ id, name, orig ].join(',');
+ }
+ byId('aliases').value = a.join('|||');
+}
+
+function salDel() {
+ var tr = this;
+ while (tr.nodeName.toLowerCase() != 'tr')
+ tr = tr.parentNode;
+ byId('alias_tbl').removeChild(tr);
+ salSerialize();
+ salEmpty();
+ return false;
+}
+
+function salFormAdd() {
+ var alnew = byId('alias_new');
+ var name = byName(byClass(alnew, 'td', 'tc_name')[0], 'input')[0];
+ var orig = byName(byClass(alnew, 'td', 'tc_original')[0], 'input')[0];
+ if(name.value.length < 1)
+ return false;
+
+ salAdd(0, name.value, orig.value);
+ salSerialize();
+ name.value = '';
+ orig.value = '';
+ return false;
+}
+
+if(byId('jt_box_staffe_aliases'))
+ salLoad();
+
+
+
+
+/* S T A F F < - > V N L I N K I N G (/v+/staff/edit) */
+
+function vnsLoad() {
+ var credits = byId('credits').value.split('|||');
+ var s = {}; // staff -> { aid: [ role, note ], .. }
+ var q = []; // list of a=X paramters
+ for (var i = 0; i < credits.length && credits[i].length > 1; i++) {
+ var c = credits[i].split('-', 3); // aid, role, note
+ if (!s[c[0]])
+ q.push('a='+c[0]);
+ s[c[0]] = [ c[1], c[2] ];
+ }
+ if (q.length > 0)
+ ajax('/xml/staff.xml?'+q.join(';'), function(hs) {
+ var staff = hs.responseXML.getElementsByTagName('item');
+ for (var i = 0; i < staff.length; i++) {
+ var aid = staff[i].getAttribute('aid');
+ if (s[aid])
+ vnsAdd (staff[i], s[aid][0], s[aid][1]);
+ else
+ vnsAdd (staff[i], 'staff', '');
+ }
+ vnsEmpty();
+ }, 1);
+ else
+ vnsEmpty();
+
+ byName(byId('maincontent'), 'form')[0].onsubmit = vnsSerialize;
+
+ // dropdown search
+ dsInit(byId('credit_input'), '/xml/staff.xml?q=', function(item, tr) {
+ tr.appendChild(tag('td', { style: 'text-align: right; padding-right: 5px'}, 's'+item.getAttribute('id')));
+ tr.appendChild(tag('td', item.firstChild.nodeValue));
+ }, vnsFormAdd);
+}
+
+function vnsAdd(staff, role, note) {
+ var sid = staff.getAttribute('id');
+ var aid = staff.getAttribute('aid');
+ var tbl = byId('credits_tbl');
+
+ var rlist = tag('select', {onchange:vnsSerialize});
+ for (var i = 0; i < staff_roles.length; i++) // l10n /^_credit_/
+ rlist.appendChild(tag('option', {value:staff_roles[i], selected:staff_roles[i]==role},
+ mt('_credit_'+staff_roles[i])));
+
+ var note = tag('input', {type:'text', 'class':'text'});
+ tbl.appendChild(tag('tr', {id:'vns_a'+aid},
+ tag('td', {'class':'tc_name'},
+ tag('input', {type:'hidden', value:aid}),
+ tag('a', {href:'/s'+sid}, staff.firstChild.nodeValue)),
+ tag('td', {'class':'tc_role'}, rlist),
+ tag('td', {'class':'tc_note'}, note),
+ tag('td', {'class':'tc_del'}, tag('a', {href:'#', onclick:vnsDel}, mt('_js_remove')))
+ ));
+ vnsEmpty();
+ vnsSerialize();
+}
+
+function vnsEmpty() {
+ var x = byId('credits_loading');
+ var tbody = byId('credits_tbl');
+ var tbl = tbody.parentNode;
+ var thead = byName(tbl, 'thead');
+ if(x)
+ tbody.removeChild(x);
+ if(byName(tbody, 'tr').length < 1) {
+ tbody.appendChild(tag('tr', {id:'credits_tr_none'},
+ tag('td', {colspan:4}, mt('_vnstaffe_none'))));
+ if (thead.length)
+ tbl.removeChild(thead[0]);
+ } else {
+ if(byId('credits_tr_none'))
+ tbody.removeChild(byId('credits_tr_none'));
+ if (thead.length < 1) {
+ thead = tag('thead', tag('tr',
+ tag('td', {'class':'tc_name'}, mt('_vnstaffe_form_staff')),
+ tag('td', {'class':'tc_role'}, mt('_vnstaffe_form_role')),
+ tag('td', {'class':'tc_note'}, mt('_vnstaffe_form_note'))));
+ tbl.insertBefore(thead, tbody);
+ }
+ }
+}
+
+function vnsSerialize() {
+ var l = byName(byId('credits_tbl'), 'tr');
+ var c = [];
+ for (var i = 0; i < l.length; i++) {
+ var aid = byName(byName(l[i], 'td')[0], 'input')[0].value;
+ var role = byName(byClass(l[i], 'tc_role')[0], 'select')[0];
+ var note = byName(byClass(l[i], 'tc_note')[0], 'input')[0];
+ c.push (aid+'-'+role.options[role.selectedIndex].value+'-'+note.value);
+ }
+ byId('credits').value = c.join('|||');
+}
+
+function vnsDel() {
+ var tr = this;
+ while (tr.nodeName.toLowerCase() != 'tr')
+ tr = tr.parentNode;
+ byId('credits_tbl').removeChild(tr);
+ vnsEmpty();
+ vnsSerialize();
+ return false;
+}
+
+function vnsFormAdd(item) {
+ vnsAdd(item, 'staff', '');
+ return '';
+}
+
+if(byId('jt_box_vnstaffe_credits'))
+ vnsLoad();
+
+
/* F I L T E R S Y S T E M */
diff --git a/data/style.css b/data/style.css
index 9b9ecb9a..3c565700 100644
--- a/data/style.css
+++ b/data/style.css
@@ -565,6 +565,36 @@ div.charb td.tc2 b { margin-left: 10px }
div.charb td.tc2 b a { color: $grayedout$!important }
+/***** Staff browse *****/
+
+div.staffbrowse { padding-bottom: 10px }
+.staffbrowse ul { float: left; margin-top: -5px; margin-left: 3%; width: 28%; }
+.staffbrowse ul li { list-style-type: none; }
+.staffbrowse ul li acronym { margin-right: 5px; margin-top: 1px; }
+div.staffinfo { position: relative; float: left; margin-right: 3%; }
+div.staffdesc { margin-bottom: 10px; }
+
+
+/***** Staff edit *****/
+
+#jt_box_vnstaffe_credits table,
+#jt_box_staffe_aliases table { margin-bottom: 10px; }
+#jt_box_vnstaffe_credits h2,
+#jt_box_staffe_aliases h2 { margin: 0 0 3px 0px; }
+#jt_box_vnstaffe_credits td,
+#jt_box_staffe_aliases td { padding: 1px 2px; vertical-align: middle; }
+#jt_box_vnstaffe_credits td.tc_role,
+#jt_box_vnstaffe_credits td.tc_role select { width: 120px }
+#jt_box_vnstaffe_credits td.tc_staff,
+#jt_box_staffe_aliases td.tc_name,
+#jt_box_staffe_aliases td.tc_original { width: 200px }
+#jt_box_vnstaffe_credits td.tc_staff input,
+#jt_box_staffe_aliases td.tc_name input,
+#jt_box_staffe_aliases td.tc_original input { width: 200px }
+#jt_box_vnstaffe_credits td.tc_note,
+#jt_box_vnstaffe_credits td.tc_note input { width: 250px }
+#jt_box_vnstaffe_credits td.tc_add,
+#jt_box_staffe_aliases td.tc_add { width: 40px; text-align: left }
/***** Documentation pages *****/
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index 86b3a859..272f9610 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -76,10 +76,11 @@ sub dbCharGet {
join(', ', @select), join(' ', @join), \%where
);
- if(@$r && $o{what} =~ /(vns|traits)/) {
+ if(@$r && $o{what} =~ /vns|traits|seiyuu/) {
my %r = map {
$_->{traits} = [];
$_->{vns} = [];
+ $_->{seiyuu} = [];
($_->{cid}, $_)
} @$r;
@@ -107,6 +108,19 @@ sub dbCharGet {
{ 'cv.cid IN(!l)' => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () }
)});
}
+
+ if($o{what} =~ /seiyuu/) {
+ push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q|
+ SELECT cs.cid, sr.sid, sa.name, sa.original, cs.note
+ FROM chars_seiyuu cs
+ JOIN staff_alias sa ON sa.id = cs.aid
+ JOIN staff_rev sr ON sr.id = sa.rid
+ JOIN staff s ON sr.id = s.latest
+ !W
+ ORDER BY sa.name|,
+ { 'cs.cid IN(!l)' => [[ keys %r ]], $o{vid} ? ('vid = ?' => $o{vid}) : () }
+ )});
+ }
}
return wantarray ? ($r, $np) : $r;
}
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 3155ca56..8927e1ec 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -27,7 +27,7 @@ sub dbStats {
sub dbItemEdit {
my($self, $type, $oid, %o) = @_;
- my $fun = {qw|v vn r release p producer c char|}->{$type};
+ my $fun = {qw|v vn r release p producer c char s staff|}->{$type};
$self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
$self->dbExec('UPDATE edit_revision !H', {
'requester = ?' => $o{uid}||$self->authInfo->{id},
@@ -41,6 +41,7 @@ sub dbItemEdit {
$self->dbProducerRevisionInsert(\%o) if $type eq 'p';
$self->dbReleaseRevisionInsert( \%o) if $type eq 'r';
$self->dbCharRevisionInsert( \%o) if $type eq 'c';
+ $self->dbStaffRevisionInsert( \%o) if $type eq 's';
return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun);
}
@@ -60,10 +61,10 @@ sub dbRevisionGet {
$o{what} ||= '';
$o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid};
- my %tables = qw|v vn r releases p producers c chars|;
+ my %tables = qw|v vn r releases p producers c chars s staff|;
# what types should we join?
my @types = (
- !$o{type} ? ('v', 'r', 'p', 'c') :
+ !$o{type} ? qw(v r p c s) :
ref($o{type}) ? @{$o{type}} :
$o{type} ne 'v' ? $o{type} :
$o{releases} ? ('v', 'r') : 'v'
@@ -97,15 +98,17 @@ sub dbRevisionGet {
) : (),
$o{what} =~ /user/ ? 'JOIN users u ON h.requester = u.id' : (),
);
+ push @join, 'LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid' if grep /s/, @types;
+ my %tcolumns = qw(v vr.title r rr.title p pr.name c cr.name s sa.name);
my @select = (
qw|h.id h.type h.requester h.comments h.rev|,
q|extract('epoch' from h.added) as added|,
$o{what} =~ /user/ ? 'u.username' : (),
$o{what} =~ /item/ ? (
'COALESCE('.join(', ', map "${_}r.${_}id", @types).') AS iid',
- 'COALESCE('.join(', ', map /[pc]/ ? "${_}r.name" : "${_}r.title", @types).') AS ititle',
- 'COALESCE('.join(', ', map "${_}r.original", @types).') AS ioriginal',
+ 'COALESCE('.join(', ', map $tcolumns{$_}, @types).') AS ititle',
+ 'COALESCE('.join(', ', map /s/ ? 'sa.original' : "${_}r.original", @types).') AS ioriginal',
) : (),
);
diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm
new file mode 100644
index 00000000..7a732c4d
--- /dev/null
+++ b/lib/VNDB/DB/Staff.pm
@@ -0,0 +1,141 @@
+
+package VNDB::DB::Staff;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|dbStaffGet dbStaffRevisionInsert|;
+
+# options: results, page, id, aid, vid, search, rev
+# what: extended changes roles aliases
+sub dbStaffGet {
+ my $self = shift;
+ my %o = (
+ results => 10,
+ page => 1,
+ what => '',
+ @_
+ );
+
+ $o{search} =~ s/%//g if $o{search};
+
+ my %where = (
+ !$o{id} && !$o{rev} ? ( 's.hidden = FALSE' => 1 ) : (),
+ $o{id} ? ( ref $o{id} ? ('s.id IN(!l)' => [$o{id}]) : ('s.id = ?' => $o{id}) ) : (),
+ $o{aid} ? ( ref $o{aid} ? ('sa.id IN(!l)' => [$o{aid}]) : ('sa.id = ?' => $o{aid}) ) : (),
+ $o{vid} ? ( 'vr.vid = ?' => $o{vid}) : (),
+ $o{search} ?
+ ( '(sa.name ILIKE ? OR sa.original ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..2 ] ) : (),
+ $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (),
+ defined $o{char} && !$o{char} ?
+ ( '(ASCII(sa.name) < 97 OR ASCII(sa.name) > 122) AND (ASCII(sa.name) < 65 OR ASCII(sa.name) > 90)' => 1 ) : (),
+ $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (),
+ );
+
+ my @join;
+ push @join, 'JOIN staff s ON '.($o{rev} ? 's.id = sr.sid' : 'sr.id = s.latest');
+ push @join, 'JOIN staff_alias sa ON sa.rid = sr.id'.($o{id}?' AND sa.id = sr.aid':'');
+ push @join, 'JOIN changes c ON c.id = sr.id' if $o{what} =~ /changes/ || $o{rev};
+ push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/;
+ push @join,
+ 'JOIN vn_staff vs ON vs.aid = sa.id',
+ 'JOIN vn_rev vr ON vs.vid = vr.id',
+ 'JOIN vn v ON vr.id = v.latest' if $o{vid};
+# fetch both staff and seiyuu in one query
+# push @join, q|
+# LEFT JOIN vn_staff vs ON vs.aid = sa.id
+# LEFT JOIN (chars_seiyuu cs JOIN chars c ON cs.cid = c.latest)
+# ON cs.aid = sa.id
+# JOIN (vn_rev vr JOIN vn v ON vr.id = v.latest)
+# ON vs.vid = vr.id OR cs.vid = v.id
+# | if $o{vid};
+
+ my $select = 's.id, sr.aid, sa.name, sa.original, sr.gender, sr.lang, sr.id AS cid';
+ $select .= ', sr.desc, sr.l_wp, s.hidden, s.locked' if $o{what} =~ /extended/;
+ $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, s.latest, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/;
+ $select .= ', vs.role, vs.note' if $o{vid};
+
+ my $order = $o{vid} ? 'ORDER BY vs.role ASC, sa.name ASC' : 'ORDER BY sa.name ASC';
+
+ my($r, $np) = $self->dbPage(\%o, q|
+ SELECT !s
+ FROM staff_rev sr
+ !s
+ !W
+ !s|,
+ $select, join(' ', @join), \%where, $order
+ );
+
+ if (@$r && $o{what} =~ /roles|aliases/) {
+ my %r = map {
+ $_->{roles} = [];
+ $_->{cast} = [];
+ $_->{aliases} = [];
+ ($_->{cid}, $_);
+ } @$r;
+ if ($o{what} =~ /roles/) {
+ push @{$r{ delete $_->{rid} }{roles}}, $_ for (@{$self->dbAll(q|
+ SELECT sa.rid, vr.vid, sa.name, v.c_released, vr.title, vr.original AS t_original, vs.role, vs.note
+ FROM vn_staff vs
+ JOIN vn_rev vr ON vr.id = vs.vid
+ JOIN vn v ON v.latest = vr.id
+ JOIN staff_alias sa ON vs.aid = sa.id
+ WHERE sa.rid IN(!l)
+ ORDER BY v.c_released ASC, vr.title ASC, vs.role ASC|, [ keys %r ]
+ )});
+ push @{$r{ delete $_->{rid} }{cast}}, $_ for (@{$self->dbAll(q|
+ SELECT sa.rid, vr.vid, sa.name, v.c_released, vr.title, vr.original AS t_original, cr.cid, cr.name AS c_name, cs.note
+ FROM chars_seiyuu cs
+ JOIN chars_rev cr ON cr.id = cs.cid
+ JOIN vn v ON v.id = cs.vid
+ JOIN vn_rev vr ON v.latest = vr.id
+ JOIN staff_alias sa ON cs.aid = sa.id
+ WHERE sa.rid IN(!l)
+ ORDER BY v.c_released ASC, vr.title ASC|, [ keys %r ]
+ )});
+ }
+ if ($o{what} =~ /aliases/) {
+ push @{$r{ delete $_->{rid} }{aliases}}, $_ for (@{$self->dbAll(q|
+ SELECT sa.id, sa.rid, sa.name, sa.original
+ FROM staff_alias sa
+ JOIN staff_rev sr ON sr.id = sa.rid
+ WHERE sr.id IN(!l) AND sr.aid <> sa.id
+ ORDER BY sa.name ASC|, [ keys %r ]
+ )});
+ }
+ }
+
+ return wantarray ? ($r, $np) : $r;
+}
+
+# Updates the edit_* tables, used from dbItemEdit()
+# Arguments: { columns in staff_rev and staff_alias},
+sub dbStaffRevisionInsert {
+ my($self, $o) = @_;
+
+ $self->dbExec('DELETE FROM edit_staff_aliases');
+ if ($o->{aid}) {
+ $self->dbExec(q|
+ INSERT INTO edit_staff_aliases (id, name, original) VALUES (?, ?, ?)|,
+ $o->{aid}, $o->{name}, $o->{original});
+ } else {
+ $o->{aid} = $self->dbRow(q|
+ INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?) RETURNING id|,
+ $o->{name}, $o->{original})->{id};
+ }
+
+ my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
+ qw|aid image gender lang desc l_wp|;
+ $self->dbExec('UPDATE edit_staff !H', \%staff) if %staff;
+ for my $alias (@{$o->{aliases}}) {
+ if ($alias->[0]) {
+ $self->dbExec('INSERT INTO edit_staff_aliases (id, name, original) VALUES (!l)', $alias);
+ } else {
+ $self->dbExec('INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?)',
+ $alias->[1], $alias->[2]);
+ }
+ }
+}
+
+1;
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 090f9ecb..f6635c07 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -7,7 +7,7 @@ use Exporter 'import';
use VNDB::Func 'gtintype', 'normalize_query';
use Encode 'decode_utf8';
-our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar|;
+our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff|;
# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
@@ -225,6 +225,13 @@ sub dbVNRevisionInsert {
my $q = join ',', map '(?)', @{$o->{anime}};
$self->dbExec("INSERT INTO edit_vn_anime (aid) VALUES $q", @{$o->{anime}}) if @{$o->{anime}};
}
+
+ if($o->{credits}) {
+ $self->dbExec('DELETE FROM edit_vn_staff');
+ my $q = join ',', ('(?, ?, ?)') x @{$o->{credits}};
+ my @val = map @{$_}[0..2], @{$o->{credits}};
+ $self->dbExec("INSERT INTO edit_vn_staff (aid, role, note) VALUES $q", @val) if @val;
+ }
}
@@ -290,5 +297,14 @@ sub dbVNHasChar {
)->{exists};
}
+
+sub dbVNHasStaff {
+ my($self, $vid) = @_;
+ return $self->dbRow(
+ 'SELECT 1 AS exists FROM vn v JOIN vn_staff vs ON v.latest = vs.vid WHERE v.id = ?', $vid
+ )->{exists};
+}
+
+
1;
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index 7ae95e43..cb829e4c 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -22,7 +22,7 @@ sub page {
my $r = $self->dbCharGet(
id => $id,
- what => 'extended traits vns'.($rev ? ' changes' : ''),
+ what => 'extended traits vns seiyuu'.($rev ? ' changes' : ''),
$rev ? ( rev => $rev ) : ()
)->[0];
return $self->resNotFound if !$r->{id};
@@ -61,6 +61,9 @@ sub page {
$_->{rid}?sprintf('[<a href="/r%d">r%d</a>]', $_->{rid}, $_->{rid}):'',
mt("_charrole_$_->{role}"), mt("_spoil_$_->{spoil}")), @{$_[0]};
}],
+ [ seiyuu => join => '<br />', split => sub {
+ map sprintf('<a href="/s%d">%s</a>', $_->{sid}, $_->{name}), @{$_[0]}
+ }],
);
}
@@ -226,6 +229,17 @@ sub charTable {
end;
}
+ if (@{$r->{seiyuu}}) {
+ Tr;
+ td class => 'key', mt '_charp_voice';
+ td;
+ for my $s (@{$r->{seiyuu}}) {
+ a href => "/s$s->{sid}", $s->{name}; br;
+ }
+ end;
+ end;
+ }
+
# description
if($r->{desc}) {
Tr class => 'nostripe';
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 357779bc..1540c5fe 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -11,7 +11,7 @@ use POSIX 'strftime';
TUWF::register(
qr{}, \&homepage,
- qr{(?:([upvrc])([1-9]\d*)/)?hist}, \&history,
+ qr{(?:([upvrcs])([1-9]\d*)/)?hist},\&history,
qr{d([1-9]\d*)}, \&docpage,
qr{setlang}, \&setlang,
qr{nospam}, \&nospam,
@@ -206,6 +206,7 @@ sub history {
$type eq 'p' ? $self->dbProducerGet(id => $id)->[0] :
$type eq 'r' ? $self->dbReleaseGet(id => $id)->[0] :
$type eq 'c' ? $self->dbCharGet(id => $id)->[0] :
+ $type eq 's' ? $self->dbStaffGet(id => $id)->[0] :
$type eq 'v' ? $self->dbVNGet(id => $id)->[0] : undef;
my $title = mt $type ? ('_hist_title_item', $obj->{title} || $obj->{name} || $obj->{username}) : '_hist_title';
return $self->resNotFound if $type && !$obj->{id};
diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm
new file mode 100644
index 00000000..14f0b53d
--- /dev/null
+++ b/lib/VNDB/Handler/Staff.pm
@@ -0,0 +1,412 @@
+
+package VNDB::Handler::Staff;
+
+use strict;
+use warnings;
+use TUWF qw(:html :xml xml_escape);
+use VNDB::Func;
+
+TUWF::register(
+ qr{s([1-9]\d*)(?:\.([1-9]\d*))?} => \&page,
+ qr{s(?:([1-9]\d*)(?:\.([1-9]\d*))?/edit|/new)}
+ => \&edit,
+ qr{s/([a-z0]|all)} => \&list,
+ qr{v([1-9]\d*)/staff/edit} => \&vn_edit,
+ qr{xml/staff.xml} => \&staffxml,
+);
+
+sub page {
+ my($self, $id, $rev) = @_;
+
+ my $s = $self->dbStaffGet(
+ id => $id,
+ what => 'extended aliases roles'.($rev ? ' changes' : ''),
+ $rev ? ( rev => $rev ) : ()
+ )->[0];
+ return $self->resNotFound if !$s->{id};
+
+ $self->htmlHeader(title => $s->{name}, noindex => $rev);
+ $self->htmlMainTabs('s', $s) if $id;
+ return if $self->htmlHiddenMessage('s', $s);
+
+ if($rev) {
+ my $prev = $rev && $rev > 1 && $self->dbStaffGet(id => $id, rev => $rev-1, what => 'changes extended aliases')->[0];
+ $self->htmlRevision('s', $prev, $s,
+ [ name => diff => 1 ],
+ [ original => diff => 1 ],
+ [ gender => serialize => sub { mt "_gender_$_[0]" } ],
+ [ lang => serialize => sub { "$_[0] (".mt("_lang_$_[0]").')' } ],
+ [ l_wp => htmlize => sub {
+ $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink'
+ }],
+ [ desc => diff => qr/[ ,\n\.]/ ],
+# [ image => htmlize => sub {
+# return $_[0] ? sprintf '<img src="%s" />', imgurl(ch => $_[0]) : mt '_stdiff_image_none';
+# }],
+ [ aliases => join => '<br />', split => sub {
+ map sprintf('%s%s', $_->{name}, $_->{original} ? ' ('.$_->{original}.')' : ''), @{$_[0]};
+ }],
+ );
+ }
+
+ div class => 'mainbox';
+ $self->htmlItemMessage('s', $s);
+ div class => 'staffinfo';
+ h1 $s->{name};
+ h2 class => 'alttitle';
+ cssicon "gen $s->{gender}", mt "_gender_$s->{gender}" if $s->{gender} ne 'unknown';
+ txt $s->{original} if $s->{original};
+ end;
+
+ # info table
+ table class => 'stripe';
+
+# Tr;
+# td class => 'key', mt '_staff_gender';
+# td mt "_gender_$s->{gender}";
+# end;
+ Tr;
+ td class => 'key', mt '_staff_language';
+ td mt "_lang_$s->{lang}";
+ end;
+ if (@{$s->{aliases}}) {
+ Tr;
+ td class => 'key', mt '_staff_aliases';
+ td;
+ p;
+ foreach my $alias (@{$s->{aliases}}) {
+ txt $alias->{name};
+ txt ' ('.$alias->{original}.')' if $alias->{original};
+ br;
+ }
+ end;
+ end;
+ end;
+ }
+ if ($s->{l_wp}) {
+ Tr;
+ td colspan => 2;
+ a href => "http://en.wikipedia.org/wiki/$s->{l_wp}", mt '_staff_l_wp';
+ end;
+ end;
+ }
+ end 'table';
+ end;
+
+ # description
+ if($s->{desc}) {
+ div class => 'staffdesc';
+ h2 mt '_staff_bio';
+ p;
+ lit bb2html $s->{desc}, 0, 1;
+ end;
+ end;
+ }
+
+ if (@{$s->{roles}} || @{$s->{cast}}) {
+ div class => 'staffroles';
+ table class => 'stripe';
+ thead;
+ Tr;
+ td class => 'tc1', mt '_staff_col_role';
+ td class => 'tc2', mt '_staff_col_title';
+ td class => 'tc3', mt '_staff_col_released';
+ td class => 'tc4', mt '_staff_col_note';
+ end;
+ end;
+ foreach my $r (@{$s->{roles}}) {
+ Tr;
+ td class => 'tc1', mt '_credit_'.$r->{role};
+ td class => 'tc2'; a href => "/v$r->{vid}", title => $r->{t_original}, $r->{title}; end;
+ td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end;
+ td class => 'tc4';
+ lit '('.mt('_staff_as').$r->{name}.') ' if $r->{name} ne $s->{name};
+ lit $r->{note};
+ end;
+ end;
+ }
+ if (@{$s->{cast}}) {
+ Tr;
+ td class => 'tc1', colspan => 4; b mt '_staff_col_cast'; end;
+ end;
+ }
+ foreach my $r (@{$s->{cast}}) {
+ Tr;
+ td class => 'tc1'; a href => "/c$r->{cid}", $r->{c_name}; end;
+ td class => 'tc2'; a href => "/v$r->{vid}", title => $r->{t_original}, $r->{title}; end;
+ td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end;
+ td class => 'tc4';
+ lit '('.mt('_staff_as').$r->{name}.') ' if $r->{name} ne $s->{name};
+ lit $r->{note};
+ end;
+ end;
+ }
+ end 'table';
+ end
+ }
+ clearfloat;
+ end;
+
+ $self->htmlFooter;
+}
+
+
+sub edit {
+ my($self, $sid, $rev) = @_;
+
+ my $s = $sid && $self->dbStaffGet(id => $sid, what => 'changes extended aliases', $rev ? (rev => $rev) : ())->[0];
+ return $self->resNotFound if $sid && !$s->{id};
+ $rev = undef if !$s || $s->{cid} == $s->{latest};
+
+ return $self->htmlDenied if !$self->authCan('edit')
+ || $sid && (($s->{locked} || $s->{hidden}) && !$self->authCan('dbmod'));
+
+ my %b4 = !$sid ? () : (
+ (map { $_ => $s->{$_} } qw|aid name original gender lang desc l_wp ihid ilock|),
+ aliases => join('|||', map sprintf('%d,%s,%s', $_->{id}, $_->{name}, $_->{original}),
+ sort { $a->{name} <=> $b->{name} } @{$s->{aliases}}),
+ );
+ my $frm;
+
+ if ($self->reqMethod eq 'POST') {
+ return if !$self->authCheckCode;
+ $frm = $self->formValidate (
+ { post => 'aid', required => 0, template => 'int' },
+ { post => 'name', maxlength => 200 },
+ { post => 'original', required => 0, maxlength => 200, default => '' },
+ { post => 'desc', required => 0, maxlength => 5000, default => '' },
+ { post => 'gender', required => 0, default => 'unknown', enum => [qw|unknown m f|] },
+ { post => 'lang', enum => $self->{languages} },
+ { post => 'l_wp', required => 0, maxlength => 150, default => '' },
+ { post => 'image', required => 0, default => 0, template => 'int' },
+ { post => 'aliases', required => 0, maxlength => 5000, default => '' },
+ { post => 'editsum', required => 0, maxlength => 5000 },
+ { post => 'ihid', required => 0 },
+ { post => 'ilock', required => 0 },
+ );
+ push @{$frm->{_err}}, 'badeditsum' if !$frm->{editsum} || lc($frm->{editsum}) eq lc($frm->{desc});
+ my @aliases = map { /^(\d+),([^,]*),(.*)$/ ? [ $1, $2, $3 ]: () } split /\|\|\|/, $frm->{aliases};
+ if (!$frm->{_err}) {
+ # parse and normalize
+ $frm->{aliases} = join('|||', map sprintf('%d,%s,%s', @$_), @aliases);
+ $frm->{ihid} = $frm->{ihid} ?1:0;
+ $frm->{ilock} = $frm->{ilock}?1:0;
+
+ return $self->resRedirect("/s$sid", 'post')
+ if $sid && !grep $frm->{$_} ne $b4{$_}, keys %b4;
+ }
+ if(!$frm->{_err}) {
+ $frm->{aliases} = \@aliases;
+ my $nrev = $self->dbItemEdit ('s' => $sid ? $s->{cid} : undef, %$frm);
+ return $self->resRedirect("/s$nrev->{iid}.$nrev->{rev}", 'post');
+ }
+ }
+
+ $frm->{$_} //= $b4{$_} for keys %b4;
+ $frm->{editsum} //= sprintf 'Reverted to revision s%d.%d', $sid, $rev if $rev;
+ $frm->{lang} = 'ja' if !$sid && !defined $frm->{lang};
+
+ my $title = mt $s ? ('_staffe_title_edit', $s->{name}) : '_staffe_title_add';
+ $self->htmlHeader(title => $title, noindex => 1);
+ $self->htmlMainTabs('s', $s, 'edit') if $s;
+ $self->htmlEditMessage('s', $s, $title);
+ $self->htmlForm({ frm => $frm, action => $s ? "/s$sid/edit" : '/s/new', editsum => 1, upload => 1 },
+ staffe_geninfo => [ mt('_staffe_form_generalinfo'),
+ [ hidden => short => 'aid' ],
+ [ input => name => mt('_staffe_form_name'), short => 'name' ],
+ [ input => name => mt('_staffe_form_original'), short => 'original' ],
+ [ static => content => mt('_staffe_form_original_note') ],
+ [ text => name => mt('_staffe_form_note').'<br /><b class="standout">'.mt('_inenglish').'</b>', short => 'desc', rows => 4 ],
+ [ select => name => mt('_staffe_form_gender'),short => 'gender', options => [
+ map [ $_, mt("_gender_$_") ], qw(unknown m f) ] ],
+ [ select => name => mt('_staffe_form_lang'), short => 'lang',
+ options => [ map [ $_, "$_ (".mt("_lang_$_").')' ], sort @{$self->{languages}} ] ],
+ [ input => name => mt('_staffe_form_wikipedia'), short => 'l_wp', pre => 'http://en.wikipedia.org/wiki/' ],
+ [ static => content => '<br />' ],
+ ],
+
+ staffe_aliases => [ mt('_staffe_aliases'),
+ [ hidden => short => 'aliases' ],
+ [ static => nolabel => 1, content => sub {
+ table;
+ thead; Tr;
+ td class => 'tc_name', mt '_staffe_form_alias';
+ td class => 'tc_original', mt '_staffe_form_original_alias'; td; end;
+ end; end;
+ tbody id => 'alias_tbl';
+ # filled with javascript
+ end;
+ end;
+ h2 mt '_staffe_aliases_add';
+ table; Tr id => 'alias_new';
+ td class => 'tc_name';
+ input id => 'alias_name', type => 'text', class => 'text'; end;
+ td class => 'tc_original';
+ input id => 'alias_original', type => 'text', class => 'text'; end;
+ td class => 'tc_add';
+ a href => '#', mt '_js_add'; end;
+ end; end;
+ }],
+ ]);
+
+ $self->htmlFooter;
+}
+
+
+sub vn_edit {
+ my($self, $vid) = @_;
+
+ my $v = $self->dbVNGet(id => $vid, what => 'changes')->[0];
+ return $self->resNotFound if !$v->{id};
+
+ return $self->htmlDenied if !$self->authCan('edit')
+ || (($v->{locked} || $v->{hidden}) && !$self->authCan('dbmod'));
+
+ my $s = $self->dbStaffGet(vid => $vid);
+
+ my %b4 = (
+ vid => $vid,
+ credits => join('|||', map sprintf('%d-%s-%s', $_->{aid}, $_->{role}, $_->{note}), @$s),
+ );
+ my $frm;
+ if ($self->reqMethod eq 'POST') {
+ return if !$self->authCheckCode;
+ $frm = $self->formValidate (
+ { post => 'vid', required => 1, template => 'int' },
+ { post => 'credits', required => 0, maxlength => 5000, default => '' },
+ );
+ my @credits = map { /^(\d+)-([^-]+)-(.*)$/ ? [ $1, $2, $3 ]: () } split /\|\|\|/, $frm->{credits};
+ if (!$frm->{_err}) {
+ # parse and normalize
+ $frm->{credits} = join('|||', map sprintf('%d-%s-%s', @$_), @credits);
+
+ return $self->resRedirect("/v$vid/staff#staff", 'post')
+ if !grep $frm->{$_} ne $b4{$_}, keys %b4;
+
+ $frm->{credits} = \@credits;
+ $frm->{editsum} = 'Edit staff';
+ my $nrev = $self->dbItemEdit ('v' => $v->{cid}, %$frm);
+ return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post');
+ }
+ }
+ $frm->{$_} //= $b4{$_} for keys %b4;
+
+ my $title = mt ('_vnstaff_edit_title', $v->{title});
+ $self->htmlHeader(title => $title, noindex => 1);
+ $self->htmlMainTabs('v', $v, 'edit');
+ $self->htmlEditMessage('v', $v, $title);
+
+ $self->htmlForm({ frm => $frm, action => "/v$vid/staff/edit" },
+ vnstaffe_credits => [ mt('_vnstaff_edit_credits'),
+ [ hidden => short => 'vid' ],
+ [ hidden => short => 'credits' ],
+ [ static => nolabel => 1, content => sub {
+ table; tbody id => 'credits_tbl';
+ Tr id => 'credits_loading'; td colspan => '4', mt('_js_loading'); end;
+ end; end;
+ h2 mt '_vnstaffe_add';
+ table; Tr;
+ td class => 'tc_staff';
+ input id => 'credit_input', type => 'text', class => 'text'; end;
+ td colspan => 3, '';
+ end; end;
+ }],
+ ]);
+
+ $self->htmlFooter;
+}
+
+
+sub list {
+ my ($self, $char) = @_;
+
+ my $f = $self->formValidate(
+ { get => 'p', required => 0, default => 1, template => 'int' },
+ { get => 'q', required => 0, default => '' },
+ );
+ return $self->resNotFound if $f->{_err};
+
+ my ($list, $np) = $self->dbStaffGet(
+ $char ne 'all' ? ( char => $char ) : (),
+ $f->{q} ? ( search => $f->{q} ) : (),
+ results => 150,
+ page => $f->{p}
+ );
+
+ return $self->resRedirect('/s'.$list->[0]{id}, 'temp')
+ if $f->{q} && @$list == 1 && $f->{p} == 1;
+
+ $self->htmlHeader(title => mt '_sbrowse_title');
+
+ div class => 'mainbox';
+ h1 mt '_sbrowse_title';
+ form action => '/s/all', 'accept-charset' => 'UTF-8', method => 'get';
+ $self->htmlSearchBox('s', $f->{q});
+ end;
+ p class => 'browseopts';
+ for ('all', 'a'..'z', 0) {
+ a href => "/s/$_", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? mt('_char_all') : $_ ? uc $_ : '#';
+ }
+ end;
+ end;
+
+ my $pageurl = "/s/$char" . ($f->{q} ? "?q=$f->{q}" : '');
+ $self->htmlBrowseNavigate($pageurl, $f->{p}, $np, 't');
+ div class => 'mainbox staffbrowse';
+ h1 mt $f->{q} ? '_sbrowse_searchres' : '_sbrowse_list';
+ if(!@$list) {
+ p mt '_sbrowse_noresults';
+ } else {
+ # spread the results over 3 equivalent-sized lists
+ my $perlist = @$list/3 < 1 ? 1 : @$list/3;
+ for my $c (0..(@$list < 3 ? $#$list : 2)) {
+ ul;
+ for ($perlist*$c..($perlist*($c+1))-1) {
+ li;
+ my $gender = $list->[$_]{gender};
+ cssicon "gen $gender", mt "_gender_$gender" if $gender ne 'unknown';
+# cssicon 'lang '.$list->[$_]{lang}, mt "_lang_$list->[$_]{lang}";
+ a href => "/s$list->[$_]{id}",
+ title => $list->[$_]{original}, $list->[$_]{name};
+ end;
+ }
+ end;
+ }
+ }
+ clearfloat;
+ end 'div';
+ $self->htmlBrowseNavigate($pageurl, $f->{p}, $np, 'b');
+ $self->htmlFooter;
+}
+
+
+sub staffxml {
+ my $self = shift;
+
+ my $q = $self->formValidate(
+ { get => 'a', required => 0, multi => 1, template => 'int' },
+ { get => 's', required => 0, multi => 1, template => 'int' },
+ { get => 'q', required => 0, maxlength => 500 },
+ );
+ return $self->resNotFound if $q->{_err} || !(@{$q->{s}} || @{$q->{a}} || $q->{q});
+
+ my($list, $np) = $self->dbStaffGet(
+ @{$q->{s}} ? (id => $q->{s}) :
+ @{$q->{a}} ? (aid => $q->{a}) :
+ $q->{q} =~ /^s([1-9]\d*)/ ? (id => $1) :
+ (search => $q->{q}),
+ results => 10,
+ page => 1,
+ );
+
+ $self->resHeader('Content-type' => 'text/xml; charset=UTF-8');
+ xml;
+ tag 'staff', more => $np ? 'yes' : 'no';
+ for(@$list) {
+ tag 'item', id => $_->{id}, aid => $_->{aid}, $_->{name};
+ }
+ end;
+}
+
+1;
+__END__
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 7d170ce8..be3f8a4c 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -13,7 +13,7 @@ TUWF::register(
qr{v/rand} => \&rand,
qr{v([1-9]\d*)/rg} => \&rg,
qr{v([1-9]\d*)/releases} => \&releases,
- qr{v([1-9]\d*)/(chars)} => \&page,
+ qr{v([1-9]\d*)/(chars|staff)} => \&page,
qr{v([1-9]\d*)(?:\.([1-9]\d*))?} => \&page,
);
@@ -513,7 +513,8 @@ sub page {
my($self, $vid, $rev) = @_;
my $char = $rev && $rev eq 'chars';
- $rev = undef if $char;
+ my $staff = $rev && $rev eq 'staff';
+ $rev = undef if $char || $staff;
my $v = $self->dbVNGet(
id => $vid,
@@ -646,25 +647,38 @@ sub page {
end 'div'; # /mainbox
my $haschar = $self->dbVNHasChar($v->{id});
- if($haschar || $self->authCan('edit')) {
+ my $hasstaff = $self->dbVNHasStaff($v->{id});
+ if($haschar || $hasstaff || $self->authCan('edit')) {
ul class => 'maintabs notfirst';
- if($haschar) {
- li class => 'left '.(!$char ? ' tabselected' : ''); a href => "/v$v->{id}#main", name => 'main', mt '_vnpage_tab_main'; end;
- li class => 'left '.( $char ? ' tabselected' : ''); a href => "/v$v->{id}/chars#chars", name => 'chars', mt '_vnpage_tab_chars'; end;
+ if($haschar || $hasstaff) {
+ li class => 'left '.(!($char || $staff) && ' tabselected'); a href => "/v$v->{id}#main", name => 'main', mt '_vnpage_tab_main'; end;
+ if ($haschar) {
+ li class => 'left '.($char && ' tabselected'); a href => "/v$v->{id}/chars#chars", name => 'chars', mt '_vnpage_tab_chars'; end;
+ }
+ if ($hasstaff) {
+ li class => 'left '.($staff && ' tabselected'); a href => "/v$v->{id}/staff#staff", name => 'staff', mt '_vnpage_tab_staff'; end;
+ }
}
if($self->authCan('edit')) {
li; a href => "/c/new?vid=$v->{id}", mt '_vnpage_char_add'; end;
+ if(!$v->{locked}) {
+ li;
+ a href => "/v$v->{id}/staff/edit", mt $hasstaff ? '_vnpage_staff_edit' : '_vnpage_staff_add';
+ end;
+ }
li; a href => "/v$v->{id}/add", mt '_vnpage_rel_add'; end;
}
end;
}
- if(!$char) {
+ if($char) {
+ _chars($self, $haschar, $v);
+ } elsif ($staff) {
+ _staff($self, $hasstaff, $v);
+ } else {
_releases($self, $v, $r);
_stats($self, $v);
_screenshots($self, $v, $r) if @{$v->{screenshots}};
- } else {
- _chars($self, $haschar, $v);
}
$self->htmlFooter;
@@ -1070,5 +1084,34 @@ sub _chars {
}
+sub _staff {
+ my ($self, $has, $v) = @_;
+ my $l = $has && $self->dbStaffGet(vid => $v->{id}, results => 100);
+ return if !$has;
+ div class => 'mainbox';
+ table class => 'stripe';
+ thead;
+ Tr;
+ td class => 'tc1', mt '_staff_col_role';
+ td class => 'tc2', mt '_staff_col_credit';
+ td class => 'tc3', mt '_staff_col_note';
+ end;
+ end;
+ my $last_role = '';
+ for my $s (@$l) {
+ Tr;
+ td class => 'tc1', $s->{role} ne $last_role ? mt '_credit_'.$s->{role} : '';
+ td class => 'tc2';
+ a href => "/s$s->{id}", title => $s->{original}||$s->{name}, $s->{name};
+ end;
+ td class => 'tc3', $s->{note};
+ end;
+ $last_role = $s->{role};
+ }
+ end 'table';
+ end;
+}
+
+
1;
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index d4bba99f..8474f9c7 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -27,7 +27,7 @@ sub htmlMainTabs {
return if $type eq 'g' && !$self->authCan('tagmod');
ul class => 'maintabs';
- if($type =~ /[uvrpc]/) {
+ if($type =~ /[uvrpcs]/) {
li $sel eq 'hist' ? (class => 'tabselected') : ();
a href => "/$id/hist", mt '_mtabs_hist';
end;
@@ -73,7 +73,7 @@ sub htmlMainTabs {
}
if( $type eq 'u' && ($self->authInfo->{id} && $obj->{id} == $self->authInfo->{id} || $self->authCan('usermod'))
- || $type =~ /[vrpc]/ && $self->authCan('edit') && ((!$obj->{locked} && !$obj->{hidden}) || $self->authCan('dbmod'))
+ || $type =~ /[vrpcs]/ && $self->authCan('edit') && ((!$obj->{locked} && !$obj->{hidden}) || $self->authCan('dbmod'))
|| $type =~ /[gi]/ && $self->authCan('tagmod')
) {
li $sel eq 'edit' ? (class => 'tabselected') : ();
@@ -284,8 +284,8 @@ sub revdiff {
# Arguments: v/r/p, obj
sub htmlEditMessage {
my($self, $type, $obj, $title, $copy) = @_;
- my $num = {v => 0, r => 1, p => 2, c => 3}->{$type};
- my $guidelines = {v => 2, r => 3, p => 4, c => 12}->{$type};
+ my $num = {v => 0, r => 1, p => 2, c => 3, 's' => 4}->{$type};
+ my $guidelines = {v => 2, r => 3, p => 4, c => 12, 's' => 16}->{$type};
div class => 'mainbox';
h1 $title;
@@ -423,6 +423,7 @@ sub htmlSearchBox {
a href => '/v/all', $sel eq 'v' ? (class => 'sel') : (), mt '_searchbox_vn';
a href => '/r', $sel eq 'r' ? (class => 'sel') : (), mt '_searchbox_releases';
a href => '/p/all', $sel eq 'p' ? (class => 'sel') : (), mt '_searchbox_producers';
+ a href => '/s/all', $sel eq 's' ? (class => 'sel') : (), mt '_searchbox_staff';
a href => '/c/all', $sel eq 'c' ? (class => 'sel') : (), mt '_searchbox_chars';
a href => '/g', $sel eq 'g' ? (class => 'sel') : (), mt '_searchbox_tags';
a href => '/i', $sel eq 'i' ? (class => 'sel') : (), mt '_searchbox_traits';
diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm
index 7de7f709..128a18c7 100644
--- a/lib/VNDB/Util/LayoutHTML.pm
+++ b/lib/VNDB/Util/LayoutHTML.pm
@@ -66,6 +66,7 @@ sub _menu {
b class => 'grayedout', '> '; a href => '/g', mt '_menu_tags'; br;
a href => '/r', mt '_menu_releases'; br;
a href => '/p/all', mt '_menu_producers'; br;
+ a href => '/s/all', mt '_menu_staff'; br;
a href => '/c/all', mt '_menu_characters'; br;
b class => 'grayedout', '> '; a href => '/i', mt '_menu_traits'; br;
a href => '/u/all', mt '_menu_users'; br;
@@ -102,6 +103,7 @@ sub _menu {
if($self->authCan('edit')) {
a href => '/v/add', mt '_menu_addvn'; br;
a href => '/p/new', mt '_menu_addproducer'; br;
+ a href => '/s/new', mt '_menu_addstaff'; br;
a href => '/c/new', mt '_menu_addcharacter'; br;
}
br;
diff --git a/util/jsgen.pl b/util/jsgen.pl
index 26815423..eb7d2a8a 100755
--- a/util/jsgen.pl
+++ b/util/jsgen.pl
@@ -114,6 +114,7 @@ sub jsgen {
$common .= sprintf "blood_types = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{blood_types}};
$common .= sprintf "genders = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{genders}};
$common .= sprintf "char_roles = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{char_roles}};
+ $common .= sprintf "staff_roles = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{staff_roles}};
$common .= sprintf "L10N_LANG = [ %s ];\n", join(', ', map
sprintf('["%s","%s"]', $_, $lang{$_}{"_lang_$_"}||$lang{en}{"_lang_$_"}),
VNDB::L10N::languages());
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 360f52f6..787ae8b8 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -200,7 +200,8 @@ BEGIN
( SELECT vid FROM vn_rev WHERE id = i
UNION SELECT rid FROM releases_rev WHERE id = i
UNION SELECT cid FROM chars_rev WHERE id = i
- UNION SELECT pid FROM producers_rev WHERE id = i),
+ UNION SELECT pid FROM producers_rev WHERE id = i
+ UNION SELECT sid FROM staff_rev WHERE id = i),
COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE),
COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE)
);
@@ -226,6 +227,7 @@ BEGIN
UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i
UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i
UNION SELECT id FROM chars_rev WHERE t = 'c' AND cid = i
+ UNION SELECT id FROM staff_rev WHERE t = 's' AND sid = i
) x(id) ON x.id = c.id
ORDER BY c.id DESC
LIMIT 1;
@@ -242,6 +244,7 @@ BEGIN
WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid;
WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid;
WHEN 'c' THEN INSERT INTO chars (latest) VALUES (0) RETURNING id INTO r.iid;
+ WHEN 's' THEN INSERT INTO staff (latest) VALUES (0) RETURNING id INTO r.iid;
END CASE;
ELSE
r.iid := i;
@@ -266,8 +269,10 @@ BEGIN
ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid;
CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE edit_vn_screenshots DROP COLUMN vid;
+ CREATE TEMPORARY TABLE edit_vn_staff (LIKE vn_staff INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_staff DROP COLUMN vid;
EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
+ TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff;
END;
PERFORM edit_revtable('v', cid);
-- new VN, load defaults
@@ -279,6 +284,7 @@ BEGIN
INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid;
INSERT INTO edit_vn_relations SELECT vid2, relation, official FROM vn_relations WHERE vid1 = cid;
INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid;
+ INSERT INTO edit_vn_staff SELECT aid, role, note FROM vn_staff WHERE vid = cid;
END IF;
END;
$$ LANGUAGE plpgsql;
@@ -297,6 +303,7 @@ BEGIN
INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime;
INSERT INTO vn_relations SELECT r.cid, vid, relation, official FROM edit_vn_relations;
INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots;
+ INSERT INTO vn_staff SELECT r.cid, aid, role, note FROM edit_vn_staff;
UPDATE vn SET latest = r.cid WHERE id = r.iid;
RETURN r;
END;
@@ -454,6 +461,49 @@ $$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION edit_staff_init(cid integer) RETURNS void AS $$
+BEGIN
+ BEGIN
+ CREATE TEMPORARY TABLE edit_staff (LIKE staff_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_staff DROP COLUMN id;
+ ALTER TABLE edit_staff DROP COLUMN sid;
+ CREATE TEMPORARY TABLE edit_staff_aliases (LIKE staff_alias INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_staff_aliases DROP COLUMN rid;
+ EXCEPTION WHEN duplicate_table THEN
+ TRUNCATE edit_staff, edit_staff_aliases;
+ END;
+ PERFORM edit_revtable('s', cid);
+ -- new staff member
+ IF cid IS NULL THEN
+ INSERT INTO edit_staff (aid) VALUES (0);
+ -- load revision
+ ELSE
+ INSERT INTO edit_staff SELECT aid, image, gender, lang, "desc", l_wp FROM staff_rev WHERE id = cid;
+ INSERT INTO edit_staff_aliases SELECT id, name, original FROM staff_alias WHERE rid = cid;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_staff_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ IF (SELECT COUNT(*) FROM edit_staff) <> 1 THEN
+ RAISE 'edit_staff must have exactly one row!';
+ END IF;
+ SELECT INTO r * FROM edit_commit();
+ INSERT INTO staff_alias (id, rid, name, original)
+ SELECT id, r.cid, name, original FROM edit_staff_aliases;
+ INSERT INTO staff_rev (id, sid, aid, image, gender, lang, "desc", l_wp)
+ SELECT r.cid, r.iid, aid, image, gender, lang, "desc", l_wp FROM edit_staff;
+ UPDATE staff SET latest = r.cid WHERE id = r.iid;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
----------------------------------------------------------
diff --git a/util/sql/staff.sql b/util/sql/staff.sql
new file mode 100644
index 00000000..074aacae
--- /dev/null
+++ b/util/sql/staff.sql
@@ -0,0 +1,59 @@
+-- database schema for staff/seiyuu
+
+ALTER TYPE dbentry_type ADD VALUE 's';
+CREATE TYPE credit_type AS ENUM ('script', 'chardesign', 'music', 'director', 'art', 'songs', 'staff');
+
+CREATE TABLE staff (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE
+);
+
+CREATE TABLE staff_rev (
+ id integer NOT NULL PRIMARY KEY,
+ sid integer NOT NULL, -- references staff
+ aid integer NOT NULL, -- true name, references staff_alias
+ image integer NOT NULL DEFAULT 0,
+ gender gender NOT NULL DEFAULT 'unknown',
+ lang language NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT ''
+);
+
+CREATE TABLE staff_alias (
+ id SERIAL NOT NULL,
+ rid integer, -- references staff_rev
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ PRIMARY KEY (id, rid)
+);
+
+CREATE TABLE vn_staff (
+ vid integer NOT NULL, -- vn_rev reference
+ aid integer NOT NULL, -- staff_alias reference
+ role credit_type NOT NULL DEFAULT 'staff',
+ note varchar(250) NOT NULL DEFAULT ''
+);
+
+CREATE TABLE chars_seiyuu (
+ cid integer NOT NULL, -- chars_rev reference
+ vid integer NOT NULL, -- vn reference
+ aid integer NOT NULL, -- staff_alias reference
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (cid, vid, aid)
+);
+
+ALTER TABLE staff ADD FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_alias ADD FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE staff_rev ADD FOREIGN KEY (sid) REFERENCES staff (id);
+ALTER TABLE staff_rev ADD FOREIGN KEY (aid,id) REFERENCES staff_alias (id,rid);
+ALTER TABLE vn_staff ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE chars_seiyuu ADD FOREIGN KEY (cid) REFERENCES chars_rev (id);
+ALTER TABLE chars_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn (id);
+
+CREATE INDEX chars_seiyuu_pkey ON chars_seiyuu (cid, vid);
+CREATE INDEX chars_seiyuu_aid ON chars_seiyuu (aid);
+CREATE INDEX vn_staff_vid ON vn_staff (vid);
+CREATE INDEX vn_staff_aid ON vn_staff (aid);