summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Chars.pm
blob: 272f96103150866adf9f27d35c26d90c9d46d2ba (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

package VNDB::DB::Chars;

use strict;
use warnings;
use Exporter 'import';

our @EXPORT = qw|dbCharGet dbCharRevisionInsert dbCharImageId|;


# options: id rev instance tagspoil trait_inc trait_exc char what results page gender bloodt
#   bust_min bust_max waist_min waist_max hip_min hip_max height_min height_max weight_min weight_max role
# what: extended traits vns changes
sub dbCharGet {
  my $self = shift;
  my %o = (
    page => 1,
    results => 10,
    what => '',
    tagspoil => 0,
    @_
  );

  $o{search} =~ s/%//g if $o{search};

  my %where = (
    !$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (),
    $o{id}  ? ( 'c.id = ?'  => $o{id} ) : (),
    $o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (),
    $o{notid}    ? ( 'c.id <> ?'   => $o{notid} ) : (),
    $o{instance} ? ( 'cr.main = ?' => $o{instance} ) : (),
    $o{vid}      ? ( 'cr.id IN(SELECT cid FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (),
    defined $o{gender} ? ( 'cr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
    defined $o{bloodt} ? ( 'cr.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (),
    defined $o{bust_min} ? ( 'cr.s_bust >= ?' => $o{bust_min} ) : (),
    defined $o{bust_max} ? ( 'cr.s_bust <= ? AND cr.s_bust > 0' => $o{bust_max} ) : (),
    defined $o{waist_min} ? ( 'cr.s_waist >= ?' => $o{waist_min} ) : (),
    defined $o{waist_max} ? ( 'cr.s_waist <= ? AND cr.s_waist > 0' => $o{waist_max} ) : (),
    defined $o{hip_min} ? ( 'cr.s_hip >= ?' => $o{hip_min} ) : (),
    defined $o{hip_max} ? ( 'cr.s_hip <= ? AND cr.s_hip > 0' => $o{hip_max} ) : (),
    defined $o{height_min} ? ( 'cr.height >= ?' => $o{height_min} ) : (),
    defined $o{height_max} ? ( 'cr.height <= ? AND cr.height > 0' => $o{height_max} ) : (),
    defined $o{weight_min} ? ( 'cr.weight >= ?' => $o{weight_min} ) : (),
    defined $o{weight_max} ? ( 'cr.weight <= ? AND cr.weight > 0' => $o{weight_max} ) : (),
    $o{search} ? (
      '(cr.name ILIKE ? OR cr.original ILIKE ? OR cr.alias ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..3 ] ) : (),
    $o{char} ? (
      'LOWER(SUBSTR(cr.name, 1, 1)) = ?' => $o{char} ) : (),
    defined $o{char} && !$o{char} ? (
      '(ASCII(cr.name) < 97 OR ASCII(cr.name) > 122) AND (ASCII(cr.name) < 65 OR ASCII(cr.name) > 90)' => 1 ) : (),
    $o{role} ? (
      'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.cid = cr.id AND cvi.role IN(!l))',
      [ ref $o{role} ? $o{role} : [$o{role}] ] ) : (),
    $o{trait_inc} ? (
      'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)',
      [ ref $o{trait_inc} ? $o{trait_inc} : [$o{trait_inc}], $o{tagspoil}, ref $o{trait_inc} ? $#{$o{trait_inc}}+1 : 1 ]) : (),
    $o{trait_exc} ? (
      'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (),
  );

  my @select = (qw|c.id cr.name cr.original cr.gender|, 'cr.id AS cid');
  push @select, qw|c.hidden c.locked cr.alias cr.desc cr.image cr.b_month cr.b_day cr.s_bust cr.s_waist cr.s_hip cr.height cr.weight cr.bloodt cr.main cr.main_spoil| if $o{what} =~ /extended/;
  push @select, qw|h.requester h.comments c.latest u.username h.rev h.ihid h.ilock|, "extract('epoch' from h.added) as added" if $o{what} =~ /changes/;

  my @join;
  push @join, $o{rev} ? 'JOIN chars c ON c.id = cr.cid' : 'JOIN chars c ON cr.id = c.latest';
  push @join, 'JOIN changes h ON h.id = cr.id' if $o{what} =~ /changes/ || $o{rev};
  push @join, 'JOIN users u ON u.id = h.requester' if $o{what} =~ /changes/;

  my($r, $np) = $self->dbPage(\%o, q|
    SELECT !s
      FROM chars_rev cr
      !s
      !W
      ORDER BY cr.name|,
    join(', ', @select), join(' ', @join), \%where
  );

  if(@$r && $o{what} =~ /vns|traits|seiyuu/) {
    my %r = map {
      $_->{traits} = [];
      $_->{vns} = [];
      $_->{seiyuu} = [];
      ($_->{cid}, $_)
    } @$r;

    if($o{what} =~ /traits/) {
      push @{$r{ delete $_->{cid} }{traits}}, $_ for (@{$self->dbAll(q|
        SELECT ct.cid, ct.tid, ct.spoil, t.name, t."group", tg.name AS groupname
          FROM chars_traits ct
          JOIN traits t ON t.id = ct.tid
          LEFT JOIN traits tg ON tg.id = t."group"
         WHERE cid IN(!l)
         ORDER BY tg."order", t.name|, [ keys %r ]
      )});
    }

    if($o{what} =~ /vns(?:\((\d+)\))?/) {
      push @{$r{ delete $_->{cid} }{vns}}, $_ for (@{$self->dbAll(q|
        SELECT cv.cid, cv.vid, cv.rid, cv.spoil, cv.role, vr.title AS vntitle, rr.title AS rtitle
          FROM chars_vns cv
          JOIN vn v ON cv.vid = v.id
          JOIN vn_rev vr ON vr.id = v.latest
          LEFT JOIN releases r ON cv.rid = r.id
          LEFT JOIN releases_rev rr ON rr.id = r.latest
          !W
          ORDER BY v.c_released|,
        { '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;
}


# Updates the edit_* tables, used from dbItemEdit()
# Arguments: { columns in chars_rev + traits + vns },
sub dbCharRevisionInsert {
  my($self, $o) = @_;

  my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
    qw|name original alias desc image b_month b_day s_bust s_waist s_hip height weight bloodt gender main main_spoil|;
  $self->dbExec('UPDATE edit_char !H', \%set) if keys %set;

  if($o->{traits}) {
    $self->dbExec('DELETE FROM edit_char_traits');
    $self->dbExec('INSERT INTO edit_char_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}});
  }
  if($o->{vns}) {
    $self->dbExec('DELETE FROM edit_char_vns');
    $self->dbExec('INSERT INTO edit_char_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}});
  }
}


# fetches an ID for a new image
sub dbCharImageId {
  return shift->dbRow("SELECT nextval('charimg_seq') AS ni")->{ni};
}



1;