1 package OpenILS::Application::Storage::Publisher::asset;
2 use base qw/OpenILS::Application::Storage/;
3 #use OpenILS::Application::Storage::CDBI::asset;
4 #use OpenILS::Utils::Fieldmapper;
5 use OpenSRF::Utils::Logger qw/:level/;
6 use OpenSRF::EX qw/:try/;
7 use OpenSRF::Utils::JSON;
11 my $log = 'OpenSRF::Utils::Logger';
14 use MARC::File::XML ( BinaryEncoding => 'UTF-8' );
20 my $granularity = shift;
22 my $c_table = action::circulation->table;
24 if (lc($granularity) eq 'year') {
25 $granularity = ", to_char(xact_start, 'YYYY') as when";
26 } elsif (lc($granularity) eq 'month') {
27 $granularity = ", to_char(xact_start, 'YYYY-MM') as when";
28 } elsif (lc($granularity) eq 'day') {
29 $granularity = ", to_char(xact_start, 'YYYY-MM-DD') as when";
31 $granularity = ", 'total' as when";
35 SELECT COUNT(*) as count $granularity
41 if ($granularity !~ /total/o) {
42 $SQL .= ' GROUP BY 2 ORDER BY 2';
45 $log->debug("Circ count SQL [$SQL]", DEBUG);
47 return action::circulation->db_Main->selectall_hashref($SQL, 'when', {}, $copy);
49 __PACKAGE__->register_method(
50 method => 'circ_count',
51 api_name => 'open-ils.storage.asset.copy.circ_count',
56 #our $_default_subfield_map = {
60 # circulating_lib => $cl,
61 # copy_location => $sl,
62 # copy_number => $num,
65 # create_date => $date,
67 # legacy_item_type => $it,
68 # legacy_item_cat_1 => $ic1,
69 # legacy_item_cat_2 => $ic2,
74 sub import_xml_holdings {
82 my $date_format = shift || 'mm/dd/yyyy';
84 ($record) = biblio::record_entry->search_where($record);
86 return 0 unless ($record);
88 my $r = MARC::Record->new_from_xml($xml);
91 for my $f ( $r->fields( $tag ) ) {
92 next unless ($f->subfield( $map->{owning_lib} ));
98 $org_cache{ $f->subfield( $map->{owning_lib} ) }
99 || actor::org_unit->search( shortname => $f->subfield( $map->{owning_lib} ) )->next->id;
101 $org_cache{ $f->subfield( $map->{owning_lib} ) } = $ol;
103 $log->debug('Could not find library with shortname ['.$f->subfield( $map->{owning_lib} ).'] : '. shift(), ERROR);
104 $log->info('Failed holdings tag: ['.OpenSRF::Utils::JSON->perl2JSON( $f ).']');
109 $org_cache{ $f->subfield( $map->{circulating_lib} ) }
110 || actor::org_unit->search( shortname => $f->subfield( $map->{circulating_lib} ) )->next->id;
112 $org_cache{ $f->subfield( $map->{circulating_lib} ) } = $cl;
114 $log->debug('Could not find library with shortname ['.$f->subfield( $map->{circulating_lib} ).'] : '. shift(), ERROR);
115 $log->info('Failed holdings tag: ['.OpenSRF::Utils::JSON->perl2JSON( $f ).']');
118 next unless ($ol && $cl);
122 $cn = asset::call_number->find_or_create(
123 { label => $f->subfield( $map->{call_number} ),
125 record => $record->id,
131 $log->debug('Could not find or create callnumber ['.$f->subfield( $map->{call_number} )."] on record $record : ". shift(), ERROR);
132 $log->info('Failed holdings tag: ['.OpenSRF::Utils::JSON->perl2JSON( $f ).']');
137 my $create_date = $f->subfield( $map->{create_date} );
140 if ($date_format eq 'mm/dd/yyyy') {
141 ($m,$d,$y) = split '/', $create_date;
143 } elsif ($date_format eq 'dd/mm/yyyy') {
144 ($d,$m,$y) = split '/', $create_date;
146 } elsif ($date_format eq 'mm-dd-yyyy') {
147 ($m,$d,$y) = split '-', $create_date;
149 } elsif ($date_format eq 'dd-mm-yyyy') {
150 ($d,$m,$y) = split '-', $create_date;
152 } elsif ($date_format eq 'yyyy-mm-dd') {
153 ($y,$m,$d) = split '-', $create_date;
155 } elsif ($date_format eq 'yyyy/mm/dd') {
156 ($y,$m,$d) = split '/', $create_date;
160 (undef,undef,undef,$d,$m,$y) = localtime;
165 my $price = $f->subfield( $map->{price} );
166 $price =~ s/[^0-9\.]+//gso;
172 copy_number => $f->subfield( $map->{copy_number} ),
174 barcode => $f->subfield( $map->{barcode} ),
179 create_date => sprintf('%04d-%02d-%02d',$y,$m,$d),
184 $log->debug('Could not create copy ['.$f->subfield( $map->{barcode} ).'] : '. shift(), ERROR);
190 __PACKAGE__->register_method(
191 method => 'import_xml_holdings',
192 api_name => 'open-ils.storage.asset.holdings.import.xml',
198 # see /home/miker/cn_browse-test.sql for page up and down sql ...
201 sub cn_browse_pagedown {
207 my $cn = uc($args{label});
208 my $org = $args{org_unit};
209 my $depth = $args{depth};
210 my $boundry_id = $args{boundry_id};
211 my $size = $args{page_size} || 20;
214 my $table = asset::call_number->table;
216 my $descendants = "actor.org_unit_descendants($org)";
217 if (defined $depth) {
218 $descendants = "actor.org_unit_descendants($org,$depth)";
221 my $orgs = join(',', @{ asset::call_number->db_Main->selectcol_arrayref("SELECT DISTINCT id FROM $descendants;") });
233 and (oils_text_as_bytea(label) > ? or ( cn.id > ? and oils_text_as_bytea(label) = ? ))
234 and owning_lib in ($orgs)
235 order by oils_text_as_bytea(label), 4, 2
239 my $sth = asset::call_number->db_Main->prepare($sql);
240 $sth->execute($cn, $boundry_id, $cn);
241 while ( my @row = $sth->fetchrow_array ) {
242 $client->respond([@row]);
248 __PACKAGE__->register_method(
249 method => 'cn_browse_pagedown',
250 api_name => 'open-ils.storage.asset.call_number.browse.page_down',
255 sub cn_browse_pageup {
261 my $cn = uc($args{label});
262 my $org = $args{org_unit};
263 my $depth = $args{depth};
264 my $boundry_id = $args{boundry_id};
265 my $size = $args{page_size} || 20;
268 my $table = asset::call_number->table;
270 my $descendants = "actor.org_unit_descendants($org)";
271 if (defined $depth) {
272 $descendants = "actor.org_unit_descendants($org,$depth)";
275 my $orgs = join(',', @{ asset::call_number->db_Main->selectcol_arrayref("SELECT DISTINCT id FROM $descendants;") });
288 and (oils_text_as_bytea(label) < ? or ( cn.id < ? and oils_text_as_bytea(label) = ? ))
289 and owning_lib in ($orgs)
290 order by oils_text_as_bytea(label) desc, 4 desc, 2 desc
296 my $sth = asset::call_number->db_Main->prepare($sql);
297 $sth->execute($cn, $boundry_id, $cn);
298 while ( my @row = $sth->fetchrow_array ) {
299 $client->respond([@row]);
305 __PACKAGE__->register_method(
306 method => 'cn_browse_pageup',
307 api_name => 'open-ils.storage.asset.call_number.browse.page_up',
312 sub cn_browse_target {
318 my $cn = uc($args{label});
319 my $org = $args{org_unit};
320 my $depth = $args{depth};
321 my $size = $args{page_size} || 20;
322 my $topsize = $size / 2;
323 $topsize = int($topsize);
324 $bottomsize = $size - $topsize;
326 my $table = asset::call_number->table;
328 my $descendants = "actor.org_unit_descendants($org)";
329 if (defined $depth) {
330 $descendants = "actor.org_unit_descendants($org,$depth)";
333 my $orgs = join(',', @{ asset::call_number->db_Main->selectcol_arrayref("SELECT DISTINCT id FROM $descendants;") });
335 my $top_sql = <<" SQL";
346 and oils_text_as_bytea(label) < ?
347 and owning_lib in ($orgs)
348 order by oils_text_as_bytea(label) desc, 4 desc, 2 desc
354 my $bottom_sql = <<" SQL";
364 and oils_text_as_bytea(label) >= ?
365 and owning_lib in ($orgs)
366 order by oils_text_as_bytea(label),4,2
370 my $sth = asset::call_number->db_Main->prepare($top_sql);
372 while ( my @row = $sth->fetchrow_array ) {
373 $client->respond([@row]);
377 $sth = asset::call_number->db_Main->prepare($bottom_sql);
379 while ( my @row = $sth->fetchrow_array ) {
380 $client->respond([@row]);
386 __PACKAGE__->register_method(
387 method => 'cn_browse_target',
388 api_name => 'open-ils.storage.asset.call_number.browse.target',
399 my $org = shift; # hold pickup lib
402 return unless ($cp && $org);
405 my $row = action::hold_request->db_Main->selectrow_hashref(
406 'SELECT proximity AS prox FROM action.hold_copy_map WHERE hold = ? and target_copy = ?',
411 return $row->{prox} if $row;
413 # There was a bug here before.
414 # action.hold_copy_calculated_proximity() was called with a
415 # third argument, $org. Wrong. a.hccp() interprets its third
416 # argument as an optional override of copy circ lib. $org
417 # here is hold pickup lib. This had the effect of basically
418 # measuring the distance between a hold's pickup lib and
419 # itself, which is always zero, so all proximities landing in
420 # the hold copy map were zero.
422 $log->debug("Calculating copy proximity with: action.hold_copy_calculated_proximity($hold,$cp)", DEBUG);
423 $row = action::hold_request->db_Main->selectrow_hashref(
424 'SELECT action.hold_copy_calculated_proximity(?,?) AS prox',
430 return $row->{prox} if $row;
433 $cp = asset::copy->retrieve($cp) unless (ref($cp));
436 my $ol = $cp->circ_lib;
438 return (actor::org_unit_proximity->search( from_org => "$ol", to_org => "$org"))[0]->prox;
440 __PACKAGE__->register_method(
441 method => 'copy_proximity',
442 api_name => 'open-ils.storage.asset.copy.proximity',
447 sub asset_copy_location_all {
451 for my $rec ( asset::copy_location->retrieve_all ) {
452 $client->respond( $rec->to_fieldmapper );
457 __PACKAGE__->register_method(
458 method => 'asset_copy_location_all',
459 api_name => 'open-ils.storage.direct.asset.copy_location.retrieve.all',
464 # XXX arg, with the descendancy SPs...
465 sub ranged_asset_copy_location {
470 my $ctable = asset::copy_location->table;
472 my $descendants = defined($binds[1]) ?
473 "actor.org_unit_full_path(?, ?)" :
474 "actor.org_unit_full_path(?)" ;
481 ON (d.id = c.owning_lib)
485 my $sth = asset::copy_location->db_Main->prepare($sql);
486 $sth->execute(@binds);
488 while ( my $rec = $sth->fetchrow_hashref ) {
490 my $cnct = new Fieldmapper::asset::copy_location;
491 map {$cnct->$_($$rec{$_})} keys %$rec;
492 $client->respond( $cnct );
497 __PACKAGE__->register_method(
498 method => 'ranged_asset_copy_location',
499 api_name => 'open-ils.storage.ranged.asset.copy_location.retrieve',
510 return undef unless (@ids);
512 @ids = ($ids[0]) unless ($self->api_name =~ /batch/o);
514 for my $id ( @ids ) {
516 my $cp = asset::copy->retrieve($id);
519 my $cp_fm = $cp->to_fieldmapper;
520 $cp_fm->circ_lib( $cp->circ_lib->to_fieldmapper );
521 $cp_fm->location( $cp->location->to_fieldmapper );
522 $cp_fm->status( $cp->status->to_fieldmapper );
523 $cp_fm->stat_cat_entries( [ map { $_->to_fieldmapper } $cp->stat_cat_entries ] );
525 $client->respond( $cp_fm );
530 __PACKAGE__->register_method(
531 api_name => 'open-ils.storage.fleshed.asset.copy.batch.retrieve',
532 method => 'fleshed_copy',
536 __PACKAGE__->register_method(
537 api_name => 'open-ils.storage.fleshed.asset.copy.retrieve',
538 method => 'fleshed_copy',
542 sub fleshed_copy_by_barcode {
547 my ($cp) = asset::copy->search( { barcode => $bc } );
549 return undef unless ($cp);
551 my $cp_fm = $cp->to_fieldmapper;
552 $cp_fm->circ_lib( $cp->circ_lib->to_fieldmapper );
553 $cp_fm->location( $cp->location->to_fieldmapper );
554 $cp_fm->status( $cp->status->to_fieldmapper );
558 __PACKAGE__->register_method(
559 api_name => 'open-ils.storage.fleshed.asset.copy.search.barcode',
560 method => 'fleshed_copy_by_barcode',
566 #XXX Fix stored proc calls
567 sub fleshed_asset_stat_cat {
572 @list = ($list[0]) unless ($self->api_name =~ /batch/o);
574 my $cat = asset::stat_cat->retrieve($sc);
578 my $sc_fm = $cat->to_fieldmapper;
579 $sc_fm->entries( [ map { $_->to_fieldmapper } $cat->entries ] );
580 $client->respond( $sc_fm );
585 __PACKAGE__->register_method(
586 api_name => 'open-ils.storage.fleshed.asset.stat_cat.retrieve',
588 method => 'fleshed_asset_stat_cat',
591 __PACKAGE__->register_method(
592 api_name => 'open-ils.storage.fleshed.asset.stat_cat.retrieve.batch',
595 method => 'fleshed_asset_stat_cat',
599 #XXX Fix stored proc calls
600 sub ranged_asset_stat_cat {
605 return undef unless ($ou);
606 my $s_table = asset::stat_cat->table;
608 my $select = <<" SQL";
611 JOIN actor.org_unit_full_path(?) p ON (p.id = s.owner)
616 $fleshed = 1 if ($self->api_name =~ /fleshed/o);
618 my $sth = asset::stat_cat->db_Main->prepare_cached($select);
621 for my $sc ( map { asset::stat_cat->construct($_) } $sth->fetchall_hash ) {
622 my $sc_fm = $sc->to_fieldmapper;
624 [ $self->method_lookup( 'open-ils.storage.ranged.asset.stat_cat_entry.search.stat_cat' )->run($ou,$sc->id) ]
626 $client->respond( $sc_fm );
631 __PACKAGE__->register_method(
632 api_name => 'open-ils.storage.ranged.fleshed.asset.stat_cat.all',
635 method => 'ranged_asset_stat_cat',
638 __PACKAGE__->register_method(
639 api_name => 'open-ils.storage.ranged.asset.stat_cat.all',
642 method => 'ranged_asset_stat_cat',
646 #XXX Fix stored proc calls
647 sub multiranged_asset_stat_cat {
652 return undef unless (defined($ous) and @$ous);
653 my $s_table = asset::stat_cat->table;
655 my $select = <<" SQL";
658 WHERE s.owner IN ( XXX )
662 my $collector = ' INTERSECT ';
663 my $entry_method = 'open-ils.storage.multiranged.intersect.asset.stat_cat_entry.search.stat_cat';
664 if ($self->api_name =~ /union/o) {
665 $collector = ' UNION ';
666 $entry_method = 'open-ils.storage.multiranged.union.asset.stat_cat_entry.search.stat_cat';
669 my $binds = join($collector, map { 'SELECT id FROM actor.org_unit_full_path(?)' } grep {defined} @$ous);
670 $select =~ s/XXX/$binds/so;
673 $fleshed = 1 if ($self->api_name =~ /fleshed/o);
675 my $sth = asset::stat_cat->db_Main->prepare_cached($select);
676 $sth->execute(map { "$_" } grep {defined} @$ous);
678 for my $sc ( map { asset::stat_cat->construct($_) } $sth->fetchall_hash ) {
679 my $sc_fm = $sc->to_fieldmapper;
681 [ $self->method_lookup( $entry_method )->run($ous, $sc->id) ]
683 $client->respond( $sc_fm );
688 __PACKAGE__->register_method(
689 api_name => 'open-ils.storage.multiranged.intersect.fleshed.asset.stat_cat.all',
692 method => 'multiranged_asset_stat_cat',
694 __PACKAGE__->register_method(
695 api_name => 'open-ils.storage.multiranged.union.fleshed.asset.stat_cat.all',
698 method => 'multiranged_asset_stat_cat',
701 #XXX Fix stored proc calls
702 sub ranged_asset_stat_cat_entry {
708 return undef unless ($ou);
709 my $s_table = asset::stat_cat_entry->table;
711 my $select = <<" SQL";
714 JOIN actor.org_unit_full_path(?) p ON (p.id = s.owner)
719 my $sth = asset::stat_cat->db_Main->prepare_cached($select);
720 $sth->execute($ou,$sc);
722 for my $sce ( map { asset::stat_cat_entry->construct($_) } $sth->fetchall_hash ) {
723 $client->respond( $sce->to_fieldmapper );
728 __PACKAGE__->register_method(
729 api_name => 'open-ils.storage.ranged.asset.stat_cat_entry.search.stat_cat',
732 method => 'ranged_asset_stat_cat_entry',
735 #XXX Fix stored proc calls
736 sub multiranged_asset_stat_cat_entry {
742 return undef unless (defined($ous) and @$ous);
743 my $s_table = asset::stat_cat_entry->table;
745 my $collector = ' INTERSECT ';
746 $collector = ' UNION ' if ($self->api_name =~ /union/o);
748 my $select = <<" SQL";
751 WHERE s.owner IN ( XXX ) and s.stat_cat = ?
755 my $binds = join($collector, map { 'SELECT id FROM actor.org_unit_full_path(?)' } grep {defined} @$ous);
756 $select =~ s/XXX/$binds/so;
758 my $sth = asset::stat_cat->db_Main->prepare_cached($select);
759 $sth->execute(map {"$_"} @$ous,$sc);
761 for my $sce ( map { asset::stat_cat_entry->construct($_) } $sth->fetchall_hash ) {
762 $client->respond( $sce->to_fieldmapper );
767 __PACKAGE__->register_method(
768 api_name => 'open-ils.storage.multiranged.intersect.asset.stat_cat_entry.search.stat_cat',
771 method => 'multiranged_asset_stat_cat_entry',
773 __PACKAGE__->register_method(
774 api_name => 'open-ils.storage.multiranged.union.asset.stat_cat_entry.search.stat_cat',
777 method => 'multiranged_asset_stat_cat_entry',
786 my $depth = shift || 0;
791 ->selectcol_arrayref(
792 'SELECT id FROM actor.org_unit_descendants(?,?)',
798 return undef unless ($ou_list and @$ou_list);
800 $cn = asset::call_number->retrieve( $cn );
801 return undef unless ($cn);
802 return undef if ($cn->deleted);
804 my $call_number = $cn->to_fieldmapper;
805 $call_number->copies([]);
807 $call_number->record( $cn->record->to_fieldmapper );
808 $call_number->record->fixed_fields( $cn->record->record_descriptor->next->to_fieldmapper );
810 for my $cp ( $cn->copies(circ_lib => $ou_list) ) {
811 next if ($cp->deleted);
812 my $copy = $cp->to_fieldmapper;
813 $copy->status( $cp->status->to_fieldmapper );
814 $copy->location( $cp->location->to_fieldmapper );
816 push @{ $call_number->copies }, $copy;
821 __PACKAGE__->register_method(
822 api_name => 'open-ils.storage.asset.call_number.ranged_tree',
823 method => 'cn_ranged_tree',
829 # XXX Since this is all we need in open-ils.storage for serial stuff ATM, just
830 # XXX putting it here instead of creating a whole new file.
831 sub issuance_ranged_tree {
836 my $depth = shift || 0;
841 ->selectcol_arrayref(
842 'SELECT id FROM actor.org_unit_descendants(?,?)',
848 return undef unless ($ou_list and @$ou_list);
850 $iss = serial::issuance->retrieve( $iss );
851 return undef unless ($iss);
853 my $issuance = $iss->to_fieldmapper;
854 $issuance->items([]);
856 # Now, gather issuances on the same bib, with the same label and date_published ...
857 my @subs = map { $_->id } serial::subscription->search( record_entry => $iss->subscription->record_entry->id );
859 my @similar_iss = serial::issuance->search_where(
860 subscription => \@subs,
861 label => $iss->label,
862 date_published => $iss->date_published
865 # ... and add all /their/ items to the target issuance
866 for my $i ( @similar_iss ) {
867 for my $it ( $i->items() ) {
868 next unless $it->unit and not $it->unit->deleted;
869 next unless (grep { $it->unit->circ_lib eq $_ } @$ou_list);
871 my $unit = $it->unit->to_fieldmapper;
872 $unit->status( $it->unit->status->to_fieldmapper );
873 $unit->location( $it->unit->location->to_fieldmapper );
875 my $item = $it->to_fieldmapper;
876 $item->unit( $unit );
878 push @{ $issuance->items }, $item;
884 __PACKAGE__->register_method(
885 api_name => 'open-ils.storage.serial.issuance.ranged_tree',
886 method => 'issuance_ranged_tree',
891 sub merge_record_assets {
898 for my $source ( @sources ) {
899 $count += asset::call_number
901 ->selectcol_arrayref(
902 "SELECT asset.merge_record_assets(?,?);",
911 __PACKAGE__->register_method(
912 api_name => 'open-ils.storage.asset.merge_record_assets',
913 method => 'merge_record_assets',