107 |
} |
} |
108 |
} |
} |
109 |
|
|
110 |
sub check { |
sub count { |
111 |
my $self = shift; |
my $self = shift; |
112 |
|
|
113 |
my $field = shift; |
my $field = shift; |
114 |
|
my $where = shift; |
115 |
|
|
116 |
my $sql = "select count(*) from $field"; |
my $sql = "select count(*) from $field where upper(item) like upper(?)||'%'"; |
117 |
|
|
118 |
my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr(); |
my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr(); |
119 |
$sth->execute() || die "sql: $sql; ".$self->{dbh}->errstr(); |
$sth->execute($where) || die "sql: $sql; ".$self->{dbh}->errstr(); |
120 |
|
|
121 |
my ($total) = $sth->fetchrow_array(); |
my ($total) = $sth->fetchrow_array(); |
122 |
|
|
128 |
my $self = shift; |
my $self = shift; |
129 |
|
|
130 |
my $field = shift; |
my $field = shift; |
|
my $what = shift || 'item'; # 'item,ident' |
|
131 |
my $where = shift; |
my $where = shift; |
132 |
|
|
133 |
my $from_ord = shift || 0; |
my $from_ord = shift || 0; |
135 |
|
|
136 |
my @sql_args; |
my @sql_args; |
137 |
|
|
138 |
my $sql = "select $what,ord from $field"; |
my $sql = "select item,ord from $field"; |
139 |
|
|
140 |
if ($where) { |
if ($where) { |
141 |
my $sql2 = " select ord from $field where upper($what) like upper(?)||'%'"; |
my $sql2 = "select ord from $field where upper(item) like upper(?)||'%'"; |
142 |
my $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr(); |
my $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr(); |
143 |
|
|
144 |
$sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr(); |
$sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr(); |
145 |
if (my $row = $sth->fetchrow_hashref) { |
if (my $row = $sth->fetchrow_hashref) { |
146 |
$from_ord += $row->{ord} - 1; |
$from_ord += $row->{ord} - 1; |
147 |
|
} else { |
148 |
|
# if no match is found when searching from beginning |
149 |
|
# of word in index, try substring match anywhere |
150 |
|
$sql2 = "select ord from $field where upper(item) like '%'||upper(?)||'%'"; |
151 |
|
$sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr(); |
152 |
|
$sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr(); |
153 |
|
if (my $row = $sth->fetchrow_hashref) { |
154 |
|
$from_ord += $row->{ord} - 1; |
155 |
|
} |
156 |
} |
} |
157 |
} |
} |
158 |
$sql .= " order by ord limit $rows offset $from_ord"; |
$sql .= " order by ord limit $rows offset $from_ord"; |
187 |
|
|
188 |
my $ord = 0; |
my $ord = 0; |
189 |
foreach my $key (@keys) { |
foreach my $key (@keys) { |
190 |
$sth->execute($ord++, |
$sth->execute(++$ord, |
191 |
$c_table->{$table}->{$key}, |
$c_table->{$table}->{$key}, |
192 |
$c_count->{$table}->{$key} |
$c_count->{$table}->{$key} |
193 |
); |
); |