|
Lines 40-46
BEGIN {
Link Here
|
| 40 |
&add_tags &add_tag |
40 |
&add_tags &add_tag |
| 41 |
&delete_tag_row_by_id |
41 |
&delete_tag_row_by_id |
| 42 |
&remove_tag |
42 |
&remove_tag |
| 43 |
&delete_tag_rows_by_ids |
|
|
| 44 |
&get_approval_rows |
43 |
&get_approval_rows |
| 45 |
&blacklist |
44 |
&blacklist |
| 46 |
&whitelist |
45 |
&whitelist |
|
Lines 74-107
INIT {
Link Here
|
| 74 |
$debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n"; |
73 |
$debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n"; |
| 75 |
} |
74 |
} |
| 76 |
|
75 |
|
| 77 |
sub get_filters { |
|
|
| 78 |
my $query = "SELECT * FROM tags_filters "; |
| 79 |
my ($sth); |
| 80 |
if (@_) { |
| 81 |
$sth = C4::Context->dbh->prepare($query . " WHERE filter_id = ? "); |
| 82 |
$sth->execute(shift); |
| 83 |
} else { |
| 84 |
$sth = C4::Context->dbh->prepare($query); |
| 85 |
$sth->execute; |
| 86 |
} |
| 87 |
return $sth->fetchall_arrayref({}); |
| 88 |
} |
| 89 |
|
| 90 |
# (SELECT count(*) FROM tags_all ) as tags_all, |
| 91 |
# (SELECT count(*) FROM tags_index ) as tags_index, |
| 92 |
|
| 93 |
sub approval_counts { |
76 |
sub approval_counts { |
| 94 |
my $query = "SELECT |
77 |
return Koha::Database->new()->schema()->resultset('TagsApproval')->search( |
| 95 |
(SELECT count(*) FROM tags_approval WHERE approved= 1) as approved_count, |
78 |
{ |
| 96 |
(SELECT count(*) FROM tags_approval WHERE approved=-1) as rejected_count, |
79 |
approved => [ 1, -1, 0 ] |
| 97 |
(SELECT count(*) FROM tags_approval WHERE approved= 0) as unapproved_count |
80 |
} |
| 98 |
"; |
81 |
)->count(); |
| 99 |
my $sth = C4::Context->dbh->prepare($query); |
|
|
| 100 |
$sth->execute; |
| 101 |
my $result = $sth->fetchrow_hashref(); |
| 102 |
$result->{approved_total} = $result->{approved_count} + $result->{rejected_count} + $result->{unapproved_count}; |
| 103 |
$debug and warn "counts returned: " . Dumper $result; |
| 104 |
return $result; |
| 105 |
} |
82 |
} |
| 106 |
|
83 |
|
| 107 |
=head2 get_count_by_tag_status |
84 |
=head2 get_count_by_tag_status |
|
Lines 112-125
Takes a status and gets a count of tags with that status
Link Here
|
| 112 |
|
89 |
|
| 113 |
=cut |
90 |
=cut |
| 114 |
|
91 |
|
| 115 |
sub get_count_by_tag_status { |
92 |
sub get_count_by_tag_status { |
| 116 |
my ($status) = @_; |
93 |
my ($status) = @_; |
| 117 |
my $dbh = C4::Context->dbh; |
94 |
return Koha::Database->new()->schema()->resultset('TagsApproval')->search( |
| 118 |
my $query = |
95 |
{ |
| 119 |
"SELECT count(*) FROM tags_approval WHERE approved=?"; |
96 |
approved => $status |
| 120 |
my $sth = $dbh->prepare($query); |
97 |
} |
| 121 |
$sth->execute( $status ); |
98 |
)->count(); |
| 122 |
return $sth->fetchrow; |
|
|
| 123 |
} |
99 |
} |
| 124 |
|
100 |
|
| 125 |
sub remove_tag { |
101 |
sub remove_tag { |
|
Lines 151-182
sub remove_tag {
Link Here
|
| 151 |
} |
127 |
} |
| 152 |
|
128 |
|
| 153 |
sub delete_tag_index { |
129 |
sub delete_tag_index { |
| 154 |
(@_) or return; |
130 |
my ( $term, $biblionumber ) = @_; |
| 155 |
my $sth = C4::Context->dbh->prepare("DELETE FROM tags_index WHERE term = ? AND biblionumber = ? LIMIT 1"); |
131 |
return Koha::Database->new()->schema()->resultset('TagsIndex')->search( |
| 156 |
$sth->execute(@_); |
132 |
{ |
| 157 |
return $sth->rows || 0; |
133 |
term => $term, |
|
|
134 |
biblionumber => $biblionumber, |
| 135 |
} |
| 136 |
)->delete(); |
| 158 |
} |
137 |
} |
|
|
138 |
|
| 159 |
sub delete_tag_approval { |
139 |
sub delete_tag_approval { |
| 160 |
(@_) or return; |
140 |
my ($term) = @_; |
| 161 |
my $sth = C4::Context->dbh->prepare("DELETE FROM tags_approval WHERE term = ? LIMIT 1"); |
141 |
my $t = Koha::Database->new()->schema()->resultset('TagsApproval')->find( $term ); |
| 162 |
$sth->execute(shift); |
142 |
return $t ? $t->delete() : 0; |
| 163 |
return $sth->rows || 0; |
|
|
| 164 |
} |
143 |
} |
|
|
144 |
|
| 165 |
sub delete_tag_row_by_id { |
145 |
sub delete_tag_row_by_id { |
| 166 |
(@_) or return; |
146 |
my ( $tag_id ) = @_; |
| 167 |
my $sth = C4::Context->dbh->prepare("DELETE FROM tags_all WHERE tag_id = ? LIMIT 1"); |
147 |
my $t = Koha::Database->new()->schema()->resultset('TagsAll')->find( $tag_id ); |
| 168 |
$sth->execute(shift); |
148 |
return $t ? $t->delete() : 0; |
| 169 |
return $sth->rows || 0; |
|
|
| 170 |
} |
| 171 |
sub delete_tag_rows_by_ids { |
| 172 |
(@_) or return; |
| 173 |
my $i=0; |
| 174 |
foreach(@_) { |
| 175 |
$i += delete_tag_row_by_id($_); |
| 176 |
} |
| 177 |
($i == scalar(@_)) or |
| 178 |
warn sprintf "delete_tag_rows_by_ids tried %s tag_ids, only succeeded on $i", scalar(@_); |
| 179 |
return $i; |
| 180 |
} |
149 |
} |
| 181 |
|
150 |
|
| 182 |
sub get_tag_rows { |
151 |
sub get_tag_rows { |
|
Lines 365-391
sub get_approval_rows { # i.e., from tags_approval
Link Here
|
| 365 |
} |
334 |
} |
| 366 |
|
335 |
|
| 367 |
sub is_approved { |
336 |
sub is_approved { |
| 368 |
my $term = shift or return; |
337 |
my $term = shift or return; |
| 369 |
my $sth = C4::Context->dbh->prepare("SELECT approved FROM tags_approval WHERE term = ?"); |
|
|
| 370 |
$sth->execute($term); |
| 371 |
unless ($sth->rows) { |
| 372 |
$ext_dict and return (spellcheck($term) ? 0 : 1); # spellcheck returns empty on OK word |
| 373 |
return 0; |
| 374 |
} |
| 375 |
return $sth->fetchrow; |
| 376 |
} |
| 377 |
|
338 |
|
| 378 |
sub get_tag_index { |
339 |
my $tag = Koha::Database->new()->schema()->resultset('TagsIndex')->find( |
| 379 |
my $term = shift or return; |
340 |
{ term => $term }, |
| 380 |
my $sth; |
341 |
{ result_class => 'DBIx::Class::ResultClass::HashRefInflator' } |
| 381 |
if (@_) { |
342 |
); |
| 382 |
$sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?"); |
343 |
|
| 383 |
$sth->execute($term,shift); |
344 |
unless ($tag) { |
| 384 |
} else { |
345 |
return $ext_dict ? spellcheck($term) ? 0 : 1 : 0; |
| 385 |
$sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ?"); |
346 |
} |
| 386 |
$sth->execute($term); |
347 |
|
| 387 |
} |
348 |
return $tag; |
| 388 |
return $sth->fetchrow_hashref; |
|
|
| 389 |
} |
349 |
} |
| 390 |
|
350 |
|
| 391 |
sub whitelist { |
351 |
sub whitelist { |
|
Lines 407-412
sub whitelist {
Link Here
|
| 407 |
} |
367 |
} |
| 408 |
return scalar @_; |
368 |
return scalar @_; |
| 409 |
} |
369 |
} |
|
|
370 |
|
| 410 |
# note: there is no "unwhitelist" operation because there is no remove for Ispell. |
371 |
# note: there is no "unwhitelist" operation because there is no remove for Ispell. |
| 411 |
# The blacklist regexps should operate "in front of" the whitelist, so if you approve |
372 |
# The blacklist regexps should operate "in front of" the whitelist, so if you approve |
| 412 |
# a term mistakenly, you can still reverse it. But there is no going back to "neutral". |
373 |
# a term mistakenly, you can still reverse it. But there is no going back to "neutral". |
|
Lines 423-576
sub blacklist {
Link Here
|
| 423 |
} |
384 |
} |
| 424 |
return scalar @_; |
385 |
return scalar @_; |
| 425 |
} |
386 |
} |
| 426 |
sub add_filter { |
|
|
| 427 |
my $operator = shift; |
| 428 |
defined $operator or return; # have to test defined to allow =0 (kohaadmin) |
| 429 |
my $query = "INSERT INTO tags_blacklist (regexp,y,z) VALUES (?,?,?)"; |
| 430 |
# my $sth = C4::Context->dbh->prepare($query); |
| 431 |
return scalar @_; |
| 432 |
} |
| 433 |
sub remove_filter { |
| 434 |
my $operator = shift; |
| 435 |
defined $operator or return; # have to test defined to allow =0 (kohaadmin) |
| 436 |
my $query = "REMOVE FROM tags_blacklist WHERE blacklist_id = ?"; |
| 437 |
# my $sth = C4::Context->dbh->prepare($query); |
| 438 |
# $sth->execute($term); |
| 439 |
return scalar @_; |
| 440 |
} |
| 441 |
|
387 |
|
| 442 |
sub add_tag_approval { # or disapproval |
388 |
sub add_tag_approval { # or disapproval |
| 443 |
$debug and warn "add_tag_approval(" . join(", ",map {defined($_) ? $_ : 'UNDEF'} @_) . ")"; |
389 |
$debug and warn "add_tag_approval(" . join(", ",map {defined($_) ? $_ : 'UNDEF'} @_) . ")"; |
| 444 |
my $term = shift or return; |
390 |
my $term = shift or return; |
| 445 |
my $query = "SELECT * FROM tags_approval WHERE term = ?"; |
391 |
my $operator = shift || 0; |
| 446 |
my $sth = C4::Context->dbh->prepare($query); |
392 |
my $approval = ( @_ ? shift : 0 ); # default is unapproved |
| 447 |
$sth->execute($term); |
393 |
|
| 448 |
($sth->rows) and return increment_weight_total($term); |
394 |
my $rs = |
| 449 |
my $operator = shift || 0; |
395 |
Koha::Database->new()->schema()->resultset('TagsApproval')->search( |
| 450 |
my $approval = (@_ ? shift : 0); # default is unapproved |
396 |
{ |
| 451 |
my @exe_args = ($term); # all 3 queries will use this argument |
397 |
term => $term |
| 452 |
if ($operator) { |
398 |
} |
| 453 |
$query = "INSERT INTO tags_approval (term,approved_by,approved,date_approved) VALUES (?,?,?,NOW())"; |
399 |
); |
| 454 |
push @exe_args, $operator, $approval; |
400 |
return increment_weight_total($term) if $rs->count(); |
| 455 |
} elsif ($approval) { |
401 |
|
| 456 |
$query = "INSERT INTO tags_approval (term,approved,date_approved) VALUES (?,?,NOW())"; |
402 |
return $rs->create( |
| 457 |
push @exe_args, $approval; |
403 |
{ |
| 458 |
} else { |
404 |
term => $term, |
| 459 |
$query = "INSERT INTO tags_approval (term,date_approved) VALUES (?,NOW())"; |
405 |
approved => $approval, |
| 460 |
} |
406 |
approved_by => $operator, |
| 461 |
$debug and print STDERR "add_tag_approval query: $query\nadd_tag_approval args: (" . join(", ", @exe_args) . ")\n"; |
407 |
date_approved => \'NOW()', |
| 462 |
$sth = C4::Context->dbh->prepare($query); |
408 |
} |
| 463 |
$sth->execute(@exe_args); |
409 |
); |
| 464 |
return $sth->rows; |
|
|
| 465 |
} |
410 |
} |
| 466 |
|
411 |
|
| 467 |
sub mod_tag_approval { |
412 |
sub mod_tag_approval { |
| 468 |
my $operator = shift; |
413 |
my $operator = shift; |
| 469 |
defined $operator or return; # have to test defined to allow =0 (kohaadmin) |
414 |
defined $operator or return; # have to test defined to allow = 0 (kohaadmin) |
| 470 |
my $term = shift or return; |
415 |
my $term = shift or return; |
| 471 |
my $approval = (scalar @_ ? shift : 1); # default is to approve |
416 |
my $approval = (scalar @_ ? shift : 1); # default is to approve |
| 472 |
my $query = "UPDATE tags_approval SET approved_by=?, approved=?, date_approved=NOW() WHERE term = ?"; |
417 |
|
| 473 |
$debug and print STDERR "mod_tag_approval query: $query\nmod_tag_approval args: ($operator,$approval,$term)\n"; |
418 |
return Koha::Database->new()->schema()->resultset('TagsApproval') |
| 474 |
my $sth = C4::Context->dbh->prepare($query); |
419 |
->search( { term => $term } )->update( |
| 475 |
$sth->execute($operator,$approval,$term); |
420 |
{ |
|
|
421 |
approved_be => $operator, |
| 422 |
approved => $approval, |
| 423 |
date_approved => \'NOW()', |
| 424 |
} |
| 425 |
); |
| 476 |
} |
426 |
} |
| 477 |
|
427 |
|
| 478 |
sub add_tag_index { |
428 |
sub add_tag_index { |
| 479 |
my $term = shift or return; |
429 |
my $term = shift or return; |
| 480 |
my $biblionumber = shift or return; |
430 |
my $biblionumber = shift or return; |
| 481 |
my $query = "SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?"; |
431 |
|
| 482 |
my $sth = C4::Context->dbh->prepare($query); |
432 |
my $rs = |
| 483 |
$sth->execute($term,$biblionumber); |
433 |
Koha::Database->new()->schema()->resultset('TagsApproval')->search( |
| 484 |
($sth->rows) and return increment_weight($term,$biblionumber); |
434 |
{ |
| 485 |
$query = "INSERT INTO tags_index (term,biblionumber) VALUES (?,?)"; |
435 |
term => $term, |
| 486 |
$debug and print STDERR "add_tag_index query: $query\nadd_tag_index args: ($term,$biblionumber)\n"; |
436 |
biblionumber => $biblionumber, |
| 487 |
$sth = C4::Context->dbh->prepare($query); |
437 |
} |
| 488 |
$sth->execute($term,$biblionumber); |
438 |
); |
| 489 |
return $sth->rows; |
439 |
|
|
|
440 |
return increment_weight( $term, $biblionumber ) if $rs->count(); |
| 441 |
|
| 442 |
return $rs->create( |
| 443 |
{ |
| 444 |
term => $term, |
| 445 |
biblionumber => $biblionumber |
| 446 |
} |
| 447 |
); |
| 490 |
} |
448 |
} |
| 491 |
|
449 |
|
| 492 |
sub get_tag { # by tag_id |
450 |
sub get_tag { # by tag_id |
| 493 |
(@_) or return; |
451 |
my ($tag_id) = @_; |
| 494 |
my $sth = C4::Context->dbh->prepare(TAG_SELECT . "WHERE tag_id = ?"); |
452 |
|
| 495 |
$sth->execute(shift); |
453 |
return Koha::Database->new()->schema()->resultset('TagsAll')->find( |
| 496 |
return $sth->fetchrow_hashref; |
454 |
$tag_id, |
|
|
455 |
{ |
| 456 |
result_class => 'DBIx::Class::ResultClass::HashRefInflator', |
| 457 |
} |
| 458 |
); |
| 497 |
} |
459 |
} |
| 498 |
|
460 |
|
| 499 |
sub increment_weights { |
461 |
sub increment_weights { |
| 500 |
increment_weight(@_); |
462 |
increment_weight(@_); |
| 501 |
increment_weight_total(shift); |
463 |
increment_weight_total(shift); |
| 502 |
} |
464 |
} |
|
|
465 |
|
| 503 |
sub decrement_weights { |
466 |
sub decrement_weights { |
| 504 |
decrement_weight(@_); |
467 |
decrement_weight(@_); |
| 505 |
decrement_weight_total(shift); |
468 |
decrement_weight_total(shift); |
| 506 |
} |
469 |
} |
|
|
470 |
|
| 507 |
sub increment_weight_total { |
471 |
sub increment_weight_total { |
| 508 |
_set_weight_total('weight_total+1',shift); |
472 |
_set_weight_total('weight_total+1',shift); |
| 509 |
} |
473 |
} |
|
|
474 |
|
| 510 |
sub increment_weight { |
475 |
sub increment_weight { |
| 511 |
_set_weight('weight+1',shift,shift); |
476 |
_set_weight('weight+1',shift,shift); |
| 512 |
} |
477 |
} |
|
|
478 |
|
| 513 |
sub decrement_weight_total { |
479 |
sub decrement_weight_total { |
| 514 |
_set_weight_total('weight_total-1',shift); |
480 |
_set_weight_total('weight_total-1',shift); |
| 515 |
} |
481 |
} |
|
|
482 |
|
| 516 |
sub decrement_weight { |
483 |
sub decrement_weight { |
| 517 |
_set_weight('weight-1',shift,shift); |
484 |
_set_weight('weight-1',shift,shift); |
| 518 |
} |
485 |
} |
|
|
486 |
|
| 519 |
sub _set_weight_total { |
487 |
sub _set_weight_total { |
| 520 |
my $sth = C4::Context->dbh->prepare(" |
488 |
my ( $weight_total, $term ) = @_; |
| 521 |
UPDATE tags_approval |
489 |
|
| 522 |
SET weight_total=" . (shift) . " |
490 |
$term = |
| 523 |
WHERE term=? |
491 |
Koha::Database->new()->schema()->resultset('TagsApproval')->find($term); |
| 524 |
"); # note: CANNOT use "?" for weight_total (see the args above). |
492 |
|
| 525 |
$sth->execute(shift); # just the term |
493 |
$term->update( { weight_total => $weight_total } ) if $term; |
| 526 |
} |
494 |
} |
|
|
495 |
|
| 527 |
sub _set_weight { |
496 |
sub _set_weight { |
| 528 |
my $dbh = C4::Context->dbh; |
497 |
my ( $weight, $term, $biblionumber ) = @_; |
| 529 |
my $sth = $dbh->prepare(" |
498 |
|
| 530 |
UPDATE tags_index |
499 |
my $t = |
| 531 |
SET weight=" . (shift) . " |
500 |
Koha::Database->new()->schema()->resultset('TagsIndex') |
| 532 |
WHERE term=? |
501 |
->find( { term => $term, biblionumber => $biblionumber } ); |
| 533 |
AND biblionumber=? |
502 |
|
| 534 |
"); |
503 |
$term->update( { weight => $weight } ) if $t; |
| 535 |
$sth->execute(@_); |
|
|
| 536 |
} |
504 |
} |
| 537 |
|
505 |
|
| 538 |
sub add_tag { # biblionumber,term,[borrowernumber,approvernumber] |
506 |
sub add_tag { # biblionumber,term,[borrowernumber,approvernumber] |
| 539 |
my $biblionumber = shift or return; |
507 |
my $biblionumber = shift or return; |
| 540 |
my $term = shift or return; |
508 |
my $term = shift or return; |
| 541 |
my $borrowernumber = (@_) ? shift : 0; # the user, default to kohaadmin |
509 |
my $borrowernumber = (@_) ? shift : 0; # the user, default to kohaadmin |
| 542 |
$term =~ s/^\s+//; |
510 |
|
| 543 |
$term =~ s/\s+$//; |
511 |
$term =~ s/^\s+//; |
| 544 |
($term) or return; # must be more than whitespace |
512 |
$term =~ s/\s+$//; |
| 545 |
my $rows = get_tag_rows({biblionumber=>$biblionumber, borrowernumber=>$borrowernumber, term=>$term, limit=>1}); |
513 |
($term) or return; # must be more than whitespace |
| 546 |
my $query = "INSERT INTO tags_all |
514 |
|
| 547 |
(borrowernumber,biblionumber,term,date_created) |
515 |
my $rows = get_tag_rows( |
| 548 |
VALUES (?,?,?,NOW())"; |
516 |
{ |
| 549 |
$debug and print STDERR "add_tag query: $query\n", |
517 |
biblionumber => $biblionumber, |
| 550 |
"add_tag query args: ($borrowernumber,$biblionumber,$term)\n"; |
518 |
borrowernumber => $borrowernumber, |
| 551 |
if (scalar @$rows) { |
519 |
term => $term, |
| 552 |
$debug and carp "Duplicate tag detected. Tag not added."; |
520 |
limit => 1 |
| 553 |
return; |
521 |
} |
| 554 |
} |
522 |
); |
| 555 |
# add to tags_all regardless of approaval |
523 |
|
| 556 |
my $sth = C4::Context->dbh->prepare($query); |
524 |
if ( scalar @$rows ) { |
| 557 |
$sth->execute($borrowernumber,$biblionumber,$term); |
525 |
$debug and carp "Duplicate tag detected. Tag not added."; |
| 558 |
|
526 |
return; |
| 559 |
# then |
527 |
} |
| 560 |
if (scalar @_) { # if arg remains, it is the borrowernumber of the approver: tag is pre-approved. |
528 |
|
| 561 |
my $approver = shift; |
529 |
my $t = Koha::Database->new()->schema()->resultset('TagsAll')->create( |
| 562 |
$debug and print STDERR "term '$term' pre-approved by borrower #$approver\n"; |
530 |
{ |
| 563 |
add_tag_approval($term,$approver,1); |
531 |
borrowernumber => $borrowernumber, |
| 564 |
add_tag_index($term,$biblionumber,$approver); |
532 |
biblionumber => $biblionumber, |
| 565 |
} elsif (is_approved($term) >= 1) { |
533 |
term => $term, |
| 566 |
$debug and print STDERR "term '$term' approved by whitelist\n"; |
534 |
date_created => \'NOW()', |
| 567 |
add_tag_approval($term,0,1); |
535 |
} |
| 568 |
add_tag_index($term,$biblionumber,1); |
536 |
); |
| 569 |
} else { |
537 |
|
| 570 |
$debug and print STDERR "term '$term' NOT approved (yet)\n"; |
538 |
|
| 571 |
add_tag_approval($term); |
539 |
if ( scalar @_ ) |
| 572 |
add_tag_index($term,$biblionumber); |
540 |
{ # if arg remains, it is the borrowernumber of the approver: tag is pre-approved. |
| 573 |
} |
541 |
my $approver = shift; |
|
|
542 |
$debug |
| 543 |
and print STDERR "term '$term' pre-approved by borrower #$approver\n"; |
| 544 |
add_tag_approval( $term, $approver, 1 ); |
| 545 |
add_tag_index( $term, $biblionumber, $approver ); |
| 546 |
} |
| 547 |
elsif ( is_approved($term) >= 1 ) { |
| 548 |
$debug and print STDERR "term '$term' approved by whitelist\n"; |
| 549 |
add_tag_approval( $term, 0, 1 ); |
| 550 |
add_tag_index( $term, $biblionumber, 1 ); |
| 551 |
} |
| 552 |
else { |
| 553 |
$debug and print STDERR "term '$term' NOT approved (yet)\n"; |
| 554 |
add_tag_approval($term); |
| 555 |
add_tag_index( $term, $biblionumber ); |
| 556 |
} |
| 574 |
} |
557 |
} |
| 575 |
|
558 |
|
| 576 |
# This takes a set of tags, as returned by C<get_approval_rows> and divides |
559 |
# This takes a set of tags, as returned by C<get_approval_rows> and divides |
| 577 |
- |
|
|