From 9c45fc9e59c76e9976afafaaa09d154d2d24894b Mon Sep 17 00:00:00 2001 From: yorhel Date: Mon, 9 Jun 2008 15:46:20 +0000 Subject: Added GTIN field to releases and fixed a very old bug with form validation (this fix may break existing forms, will need a lot of testing) git-svn-id: svn://vndb.org/vndb@26 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b --- data/docs/3 | 5 +++++ data/tpl/defs.pl | 7 +++++-- data/tpl/redit | 1 + data/tpl/rpage | 3 +++ lib/ChangeLog | 1 + lib/VNDB/Releases.pm | 7 ++++--- lib/VNDB/Util/DB.pm | 9 +++++---- lib/VNDB/Util/Tools.pm | 41 ++++++++++++++++++++++++++++++++--------- util/updates/update_1.17.sql | 5 +++++ 9 files changed, 61 insertions(+), 18 deletions(-) create mode 100644 util/updates/update_1.17.sql diff --git a/data/docs/3 b/data/docs/3 index 5aeff68c..2a0b577a 100644 --- a/data/docs/3 +++ b/data/docs/3 @@ -24,6 +24,11 @@ character sets), put the original title here.
*Language
What language is this release? Use the language that the majority of the game is in. +
JAN/UPC/EAN
+ The GTIN code + of the product. Often called "JAN" for Japanese releases, "UPC" for USA and Canada + and "EAN" for Europe. The system will automatically detect the type from the code and + use the appropriate term on the release page.
Official website
..
Release date
diff --git a/data/tpl/defs.pl b/data/tpl/defs.pl index 0845fab7..3982028e 100644 --- a/data/tpl/defs.pl +++ b/data/tpl/defs.pl @@ -269,6 +269,7 @@ sub gettitle{$p{$_}&&($p{PageTitle}=ref($pagetitles{$_}) eq 'CODE' ? &{$pagetitl # F O R M E R R O R H A N D L I N G # my %formerr_names = ( + # this list is rather incomplete... mail => 'Email', username => 'Username', userpass => 'Password', @@ -285,6 +286,7 @@ my %formerr_names = ( media => 'Media', name => 'Name', vn => 'Visual novel relations', + l_vnn => 'Visual-novels.net link', ); my @formerr_msgs = ( sub { return sprintf 'Field "%s" is required.', @_ }, @@ -295,7 +297,8 @@ my @formerr_msgs = ( $_[1] eq 'url' ? 'Invalid URL' : $_[1] eq 'pname' ? sprintf('%s can only contain alfanumeric characters!', $_[0]) : $_[1] eq 'asciiprint' ? sprintf('Only ASCII characters are allowed at %s', $_[0]) : - $_[1] eq 'int' ? sprintf('%s should be a number!', $_[0]) : ''; + $_[1] eq 'int' ? sprintf('%s should be a number!', $_[0]) : + $_[1] eq 'gtin' ? 'Not a valid JAN, UPC or EAN code!' : ''; }, sub { return sprintf '%s: invalid item selected', @_ }, sub { return 'Invalid unicode, are you sure your browser works fine?' }, @@ -316,7 +319,7 @@ sub formerr { my $ret = ' Error:
    '; $ret .= sprintf "
  • %s
    • \n", - /^([a-z0-9]+)_([0-9]+)_?(.*)$/ ? &{$formerr_msgs[$2-1]}($formerr_names{$1}, $3?$3:'') : $formerr_exeptions{$_} + /^([a-z0-9_]+)_([0-9]+)_?(.*)$/ ? &{$formerr_msgs[$2-1]}($formerr_names{$1}||$1, $3||'') : $formerr_exeptions{$_} foreach (@err); $ret .= "
\n\n"; } diff --git a/data/tpl/redit b/data/tpl/redit index 4bac5e24..163bf5b6 100644 --- a/data/tpl/redit +++ b/data/tpl/redit @@ -30,6 +30,7 @@ { type => 'select', name => 'Language', short => 'language', r=>1, options => [ map { ({ short => $_, name => sprintf '%s (%s)', $_, $VNDB::LANG->{$_} }) } sort keys %{$VNDB::LANG} ] }, + { type => 'input', name => 'JAN/UPC/EAN', short => 'gtin' }, { type => 'input', name => 'Official website', short => 'website' }, { type => 'date', name => 'Release date', short => 'released' }, { type => 'static', text => 'Leave month or day blank if they are unknown

' }, diff --git a/data/tpl/rpage b/data/tpl/rpage index ac5eaa11..11432f80 100644 --- a/data/tpl/rpage +++ b/data/tpl/rpage @@ -18,6 +18,7 @@ [ type => 'Type', sub { $VNDB::RTYP->[$_[0] ] } ], [ title => 'Title', 1 ], [ original => 'Orig. title', 1 ], + [ gtin => 'JAN/UPC/EAN', 1 ], [ language => 'Language', sub { $VNDB::LANG->{$_[0]} } ], [ website => 'Website', \&summary ], [ released => 'Release date', \&datestr ], @@ -37,6 +38,8 @@
Original Title
[[: $d{rel}{original} ]]
[[ } ]]-
Language
[[: $VNDB::LANG->{$d{rel}{language}} ]]
Release date
[[= datestr($d{rel}{released}) ]]
+[[ if($d{rel}{gtin}) { ]]- +
[[: VNDB::GTINType($d{rel}{gtin}) ]]- code
[[= $d{rel}{gtin} ]]
[[ } ]]- [[ if($d{rel}{minage} >= 0) { ]]-
Age rating
[[: $VNDB::VRAGES->{$d{rel}{minage}} ]]
[[ } ]]- [[ if($#{$d{rel}{producers}} >= 0) { ]]- diff --git a/lib/ChangeLog b/lib/ChangeLog index 500effa8..263e3f1c 100644 --- a/lib/ChangeLog +++ b/lib/ChangeLog @@ -18,6 +18,7 @@ TODO: file to override all options - not available on SVN - Dynamic loading, several bugfixes, and code cleanup for Multi - Added 'School Life' category + - Added GTIN field to releases 1.16 - 2008-05-22 - Release dates in the current year or month without a specified day will diff --git a/lib/VNDB/Releases.pm b/lib/VNDB/Releases.pm index abf3a786..0e95cc5a 100644 --- a/lib/VNDB/Releases.pm +++ b/lib/VNDB/Releases.pm @@ -60,7 +60,7 @@ sub REdit { return $self->ResDenied if !$self->AuthCan('edit') || ($r->{locked} && !$self->AuthCan('lock')); my %b4 = $rid ? ( - (map { $_ => $r->{$_} } qw|title original language website notes minage type platforms|), + (map { $_ => $r->{$_} } qw|title original gtin language website notes minage type platforms|), released => $r->{released} =~ /^([0-9]{4})([0-9]{2})([0-9]{2})$/ ? [ $1, $2, $3 ] : [ 0, 0, 0 ], media => join(',', map { $_->{medium} =~ /^(cd|dvd|gdr|blr)$/ ? ($_->{medium}.'_'.$_->{qty}) : $_->{medium} } @{$r->{media}}), producers => join('|||', map { $_->{id}.','.$_->{name} } @{$r->{producers}}), @@ -73,6 +73,7 @@ sub REdit { { name => 'type', required => 1, enum => [ 0..$#{$VNDB::RTYP} ] }, { name => 'title', required => 1, maxlength => 250 }, { name => 'original', required => 0, maxlength => 250, default => '' }, + { name => 'gtin', required => 0, template => 'gtin', default => '0' }, { name => 'language', required => 1, enum => [ keys %{$VNDB::LANG} ] }, { name => 'website', required => 0, template => 'url', default => '' }, { name => 'released', required => 0, multi => 1, template => 'int', default => 0 }, @@ -101,12 +102,12 @@ sub REdit { return $self->ResRedirect('/r'.$rid, 'post') if $rid && $released == $r->{released} && (join(',', sort @{$b4{platforms}}) eq join(',', sort @{$frm->{platforms}})) && - 10 == scalar grep { $_ ne 'comm' && $_ ne 'released' && $_ ne 'platforms' && $frm->{$_} eq $b4{$_} } keys %b4; + 11 == scalar grep { $_ ne 'comm' && $_ ne 'released' && $_ ne 'platforms' && $frm->{$_} eq $b4{$_} } keys %b4; if(!$frm->{_err}) { my %opts = ( vn => $new_vn, - (map { $_ => $frm->{$_} } qw|title original language website notes minage type comm platforms|), + (map { $_ => $frm->{$_} } qw|title original gtin language website notes minage type comm platforms|), released => $released, media => $media, producers => $producers, diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm index 87b7ba89..b1372a54 100644 --- a/lib/VNDB/Util/DB.pm +++ b/lib/VNDB/Util/DB.pm @@ -865,7 +865,7 @@ sub DBGetRelease { # %options->{ id vid results page rev } push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/; push @join, 'JOIN releases_vn rv ON rv.rid = rr.id' if $o{vid}; - my $select = 'r.id, r.locked, r.hidden, rr.id AS cid, rr.title, rr.original, rr.language, rr.website, rr.released, rr.notes, rr.minage, rr.type'; + my $select = 'r.id, r.locked, r.hidden, rr.id AS cid, rr.title, rr.original, rr.gtin, rr.language, rr.website, rr.released, rr.notes, rr.minage, rr.type'; $select .= ', c.added, c.requester, c.comments, r.latest, u.username, c.prev' if $o{what} =~ /changes/; my $r = $s->DBAll(qq| @@ -981,10 +981,11 @@ sub DBEditRelease { # id, %opts->{ columns in releases_rev table + comm + vn + p sub _insert_release_rev { my($s, $cid, $rid, $o) = @_; + # most GTIN numbers can't be represented in a 32bit integer, so make sure Perl doesn't interpret it as one (%s, not %d) $s->DBExec(q| - INSERT INTO releases_rev (id, rid, title, original, language, website, released, notes, minage, type) - VALUES (%d, %d, !s, !s, !s, !s, %d, !s, %d, %d)|, - $cid, $rid, @$o{qw| title original language website released notes minage type|}); + INSERT INTO releases_rev (id, rid, title, original, gtin, language, website, released, notes, minage, type) + VALUES (%d, %d, !s, !s, %s, !s, !s, %d, !s, %d, %d)|, + $cid, $rid, @$o{qw| title original gtin language website released notes minage type|}); $s->DBExec(q| INSERT INTO releases_producers (rid, pid) diff --git a/lib/VNDB/Util/Tools.pm b/lib/VNDB/Util/Tools.pm index f338ad7c..e537d5a2 100644 --- a/lib/VNDB/Util/Tools.pm +++ b/lib/VNDB/Util/Tools.pm @@ -8,12 +8,10 @@ use Tie::ShareLite ':lock'; use Exporter 'import'; our $VERSION = $VNDB::VERSION; -our @EXPORT = qw| FormCheck AddHid SendMail AddDefaultStuff RunCmd |; +our @EXPORT = qw| FormCheck AddHid GTINType SendMail AddDefaultStuff RunCmd |; -# Improved version of ParamsCheck -# - hashref instead of hash -# - parameters don't start with form* +# ...this function could use some serious rewriting sub FormCheck { my $self = shift; my @ps = @_; @@ -39,20 +37,21 @@ sub FormCheck { || ($t eq 'pname' && $$val !~ /^[a-z0-9][a-z0-9\-]*$/) || ($t eq 'asciiprint' && $$val !~ /^[\x20-\x7E]*$/) || ($t eq 'int' && $$val !~ /^\-?[0-9]+$/) - || ($t eq 'date' && $$val !~ /^[0-9]{4}(-[0-9]{2}(-[0-9]{2})?)?$/); + || ($t eq 'date' && $$val !~ /^[0-9]{4}(-[0-9]{2}(-[0-9]{2})?)?$/) + || ($t eq 'gtin' && !GTINType($$val)); } $e = 5 if !$e && $ps[$i]{enum} && ref($ps[$i]{enum}) eq "ARRAY" && !_inarray($$val, $ps[$i]{enum}); if($e) { - if($ps[$i]{required}) { + if(!$ps[$i]{required} && !$$val && length($$val) < 1 && $$val ne '0') { + $hash{$k}[$j] = exists $ps[$i]{default} ? $ps[$i]{default} : undef; + } else { my $errc = $ps[$i]{name}.'_'.$e; $errc .= '_'.$ps[$i]{minlength} if $e == 2; $errc .= '_'.$ps[$i]{maxlength} if $e == 3; $errc .= '_'.$ps[$i]{template} if $e == 4; push(@err, $errc); last; - } else { - $hash{$k}[$j] = exists $ps[$i]{default} ? $ps[$i]{default} : undef; - } + } } last if !$ps[$i]{multi}; } @@ -71,6 +70,30 @@ sub AddHid { } +sub GTINType { # returns 'JAN', 'EAN', 'UPC' or undef + my $c = $_[0]; + return undef if $c !~ /^[0-9]{12,14}$/; # only GTIN-12, 13 and 14 codes (for now...) + $c = ('0'x(14-length $c)) . $c; # pad with zeros + + # calculate check digit according to + # http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html#how + my @n = reverse split //, $c; + my $n=0; + $n += $n[$_] * ($_ % 2 == 0 ? 1 : 3) for (1..$#n); + $n = 10 - ($n % 10); + return undef if $n != $n[0]; + + # Do some rough guesses based on: + # http://www.gs1.org/productssolutions/barcodes/support/prefix_list.html + # and http://en.wikipedia.org/wiki/List_of_GS1_country_codes + local $_ = $c; + return 'JAN' if /^04[59]/; # prefix code 450-459 & 490-499 + return 'UPC' if /^0(?:0[01]|0[6-9]|13|75[45])/; # prefix code 000-019 & 060-139 & 754-755 + return undef if /0(?:0[2-5]|2|97[789]|9[6-9])/; # some codes we don't want: 020–059 & 200-299 & 977-999 + return 'EAN'; # let's just call everything else EAN :) +} + + sub _inarray { # errr... this is from when I didn't know about grep foreach (@{$_[1]}) { (return 1) if $_[0] eq $_; diff --git a/util/updates/update_1.17.sql b/util/updates/update_1.17.sql new file mode 100644 index 00000000..6cf2a077 --- /dev/null +++ b/util/updates/update_1.17.sql @@ -0,0 +1,5 @@ + +-- Add GTIN column +ALTER TABLE releases_rev ADD COLUMN gtin bigint NOT NULL DEFAULT 0; + + -- cgit v1.2.3