View | Details | Raw Unified | Return to bug 28530
Collapse All | Expand All

(-)a/Koha/Library/FloatLimits.pm (-116 / +100 lines)
Lines 97-219 sub lowest_ratio_library { Link Here
97
    return unless @float_limits;
97
    return unless @float_limits;
98
98
99
    my @candidates;
99
    my @candidates;
100
    my $use_item_level = C4::Context->preference('item-level_itypes');
100
    my $use_item_level     = C4::Context->preference('item-level_itypes');
101
101
    my $effective_itemtype = $item->effective_itemtype;
102
    foreach my $limit (@float_limits) {
102
103
        my $branch          = $limit->get_column('branchcode');
103
    # Build hash of float limits for quick lookup
104
        my $float_limit_val = $limit->get_column('float_limit');
104
    my %float_limit_by_branch = map { $_->get_column('branchcode') => $_->get_column('float_limit') } @float_limits;
105
105
    my @branch_codes          = keys %float_limit_by_branch;
106
        my $item_count;
106
107
107
    # Get item counts at branches in a single aggregated query
108
        if ($use_item_level) {
108
    my %at_branch_counts;
109
            my $at_branch_count = Koha::Items->search(
109
    if ($use_item_level) {
110
                {
110
        my $at_branch_rs = $schema->resultset('Item')->search(
111
                    itype         => $item->effective_itemtype,
111
            {
112
                    holdingbranch => $branch,
112
                itype         => $effective_itemtype,
113
                    onloan        => undef
113
                holdingbranch => { -in => \@branch_codes },
114
                },
114
                onloan        => undef
115
            )->count;
115
            },
116
116
            {
117
            # Count items in transit TO this branch
117
                select   => [ 'holdingbranch', { count => 'itemnumber', -as => 'item_count' } ],
118
            my $in_transit_to_count = Koha::Items->search(
118
                as       => [ 'holdingbranch', 'item_count' ],
119
                {
119
                group_by => ['holdingbranch']
120
                    itype => $item->effective_itemtype,
121
122
                    # Join with active transfers where this branch is the destination
123
                },
124
                {
125
                    join  => 'branchtransfers',
126
                    where => {
127
                        'branchtransfers.tobranch'      => $branch,
128
                        'branchtransfers.datearrived'   => undef,     # Still in transit
129
                        'branchtransfers.datecancelled' => undef,     #Not cancelled
130
                    },
131
                    distinct => 1
132
                }
133
            )->count;
134
135
            my $in_transit_from_count = Koha::Items->search(
136
                { itype => $item->effective_itemtype },
137
                {
138
                    join  => 'branchtransfers',
139
                    where => {
140
                        'branchtransfers.frombranch'    => $branch,
141
                        'branchtransfers.datearrived'   => undef,     # Still in transit
142
                        'branchtransfers.datecancelled' => undef,     #Not cancelled
143
                    },
144
                    distinct => 1
145
                }
146
            )->count;
147
            $item_count = $at_branch_count + $in_transit_to_count - $in_transit_from_count;
148
149
            # artificially adjust counts for the item being checked in
150
            if ($from_branch) {
151
152
                # This is the checkin branch - artificially add 1
153
                if ( $branch eq $branchcode ) {
154
                    $item_count++;
155
                }
156
157
                # This is where the item came from - artificially subtract 1
158
                if ( $branch eq $from_branch ) {
159
                    $item_count--;
160
                }
161
            }
120
            }
162
121
        );
163
        } else {
122
        while ( my $row = $at_branch_rs->next ) {
164
            my $at_branch_count = Koha::Items->search(
123
            $at_branch_counts{ $row->get_column('holdingbranch') } = $row->get_column('item_count');
165
                {
124
        }
166
                    holdingbranch         => $branch,
125
    } else {
167
                    'biblioitem.itemtype' => $item->effective_itemtype,
126
        my $at_branch_rs = $schema->resultset('Item')->search(
168
                    onloan                => undef
127
            {
169
                },
128
                holdingbranch         => { -in => \@branch_codes },
170
            )->count;
129
                'biblioitem.itemtype' => $effective_itemtype,
171
130
                onloan                => undef
172
            # Count items in transit TO this branch
131
            },
173
            my $in_transit_to_count = Koha::Items->search(
132
            {
174
                {
133
                join     => 'biblioitem',
175
                    itype => $item->effective_itemtype,
134
                select   => [ 'me.holdingbranch', { count => 'me.itemnumber', -as => 'item_count' } ],
176
                },
135
                as       => [ 'holdingbranch',    'item_count' ],
177
                {
136
                group_by => ['me.holdingbranch']
178
                    join  => 'branchtransfers',
179
                    where => {
180
                        'branchtransfers.tobranch'      => $branch,
181
                        'branchtransfers.datearrived'   => undef,     # Still in transit
182
                        'branchtransfers.datecancelled' => undef,     #Not cancelled
183
                    },
184
                    distinct => 1
185
                }
186
            )->count;
187
188
            my $in_transit_from_count = Koha::Items->search(
189
                {
190
                    itype => $item->effective_itemtype,
191
                },
192
                {
193
                    join  => 'branchtransfers',
194
                    where => {
195
                        'branchtransfers.frombranch'    => $branch,
196
                        'branchtransfers.datearrived'   => undef,     # Still in transit
197
                        'branchtransfers.datecancelled' => undef,     #Not cancelled
198
                    },
199
                    distinct => 1
200
                }
201
            )->count;
202
            $item_count = $at_branch_count + $in_transit_to_count - $in_transit_from_count;
203
204
            # artificially adjust counts for the item being checked in
205
            if ($from_branch) {
206
207
                # This is the checkin branch - artificially add 1
208
                if ( $branch eq $branchcode ) {
209
                    $item_count++;
210
                }
211
212
                # This is where the item came from - artificially subtract 1
213
                if ( $branch eq $from_branch ) {
214
                    $item_count--;
215
                }
216
            }
137
            }
138
        );
139
        while ( my $row = $at_branch_rs->next ) {
140
            $at_branch_counts{ $row->get_column('holdingbranch') } = $row->get_column('item_count');
141
        }
142
    }
143
144
    # Get items in transit TO branches in a single aggregated query
145
    my %in_transit_to_counts;
146
    my $to_transit_rs = $schema->resultset('Item')->search(
147
        { 'me.itype' => $effective_itemtype },
148
        {
149
            join  => 'branchtransfers',
150
            where => {
151
                'branchtransfers.tobranch'      => { -in => \@branch_codes },
152
                'branchtransfers.datearrived'   => undef,
153
                'branchtransfers.datecancelled' => undef,
154
            },
155
            select => [ 'branchtransfers.tobranch', { count => { distinct => 'me.itemnumber' }, -as => 'item_count' } ],
156
            as     => [ 'tobranch',                 'item_count' ],
157
            group_by => ['branchtransfers.tobranch']
158
        }
159
    );
160
    while ( my $row = $to_transit_rs->next ) {
161
        $in_transit_to_counts{ $row->get_column('tobranch') } = $row->get_column('item_count');
162
    }
163
164
    # Get items in transit FROM branches in a single aggregated query
165
    my %in_transit_from_counts;
166
    my $from_transit_rs = $schema->resultset('Item')->search(
167
        { 'me.itype' => $effective_itemtype },
168
        {
169
            join  => 'branchtransfers',
170
            where => {
171
                'branchtransfers.frombranch'    => { -in => \@branch_codes },
172
                'branchtransfers.datearrived'   => undef,
173
                'branchtransfers.datecancelled' => undef,
174
            },
175
            select =>
176
                [ 'branchtransfers.frombranch', { count => { distinct => 'me.itemnumber' }, -as => 'item_count' } ],
177
            as       => [ 'frombranch', 'item_count' ],
178
            group_by => ['branchtransfers.frombranch']
179
        }
180
    );
181
    while ( my $row = $from_transit_rs->next ) {
182
        $in_transit_from_counts{ $row->get_column('frombranch') } = $row->get_column('item_count');
183
    }
184
185
    # Calculate ratios for each branch using the aggregated counts
186
    foreach my $branch (@branch_codes) {
187
        my $float_limit_val = $float_limit_by_branch{$branch};
188
189
        my $item_count =
190
            ( $at_branch_counts{$branch}       || 0 ) +
191
            ( $in_transit_to_counts{$branch}   || 0 ) -
192
            ( $in_transit_from_counts{$branch} || 0 );
193
194
        # Artificially adjust counts for the item being checked in
195
        if ($from_branch) {
196
197
            # This is the checkin branch - artificially add 1
198
            $item_count++ if $branch eq $branchcode;
199
200
            # This is where the item came from - artificially subtract 1
201
            $item_count-- if $branch eq $from_branch;
217
        }
202
        }
218
203
219
        # Guard against division by zero (float_limit_val should never be 0 due to search filter, but be safe)
204
        # Guard against division by zero (float_limit_val should never be 0 due to search filter, but be safe)
220
- 

Return to bug 28530