| 
      
            Lines 21-27
          
      
      
        Link Here
      
     | 
  
        
          | 21 | 
          use Modern::Perl;  | 
          21 | 
          use Modern::Perl;  | 
        
        
          | 22 | 
           | 
          22 | 
           | 
        
        
          | 23 | 
          use CGI qw ( -utf8 );  | 
          23 | 
          use CGI qw ( -utf8 );  | 
        
            
              | 24 | 
              use Date::Calc qw/Today Add_Delta_YM/;  | 
               | 
               | 
            
        
          | 25 | 
           | 
          24 | 
           | 
        
        
          | 26 | 
          use C4::Context;  | 
          25 | 
          use C4::Context;  | 
        
        
          | 27 | 
          use C4::Output;  | 
          26 | 
          use C4::Output;  | 
        
  
    | 
      
            Lines 30-36
          use C4::Debug;
      
      
        Link Here
      
     | 
  
        
          | 30 | 
          use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/;  | 
          29 | 
          use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/;  | 
        
        
          | 31 | 
          use C4::Acquisition qw/GetOrdersByBiblionumber/;  | 
          30 | 
          use C4::Acquisition qw/GetOrdersByBiblionumber/;  | 
        
        
          | 32 | 
          use Koha::DateUtils;  | 
          31 | 
          use Koha::DateUtils;  | 
        
          
            
              | 33 | 
              use Koha::Acquisition::Baskets;  | 
              32 | 
              use Koha::Util::HoldsRatios;  | 
            
        
          | 34 | 
           | 
          33 | 
           | 
        
        
          | 35 | 
          my $input = new CGI;  | 
          34 | 
          my $input = new CGI;  | 
        
        
          | 36 | 
          my $startdate       = $input->param('from'); | 
          35 | 
          my $startdate       = $input->param('from'); | 
        
  
    | 
      
            Lines 38-43
          my $enddate         = $input->param('to');
      
      
        Link Here
      
     | 
  
        
          | 38 | 
          my $ratio           = $input->param('ratio'); | 
          37 | 
          my $ratio           = $input->param('ratio'); | 
        
        
          | 39 | 
          my $include_ordered = $input->param('include_ordered'); | 
          38 | 
          my $include_ordered = $input->param('include_ordered'); | 
        
        
          | 40 | 
           | 
          39 | 
           | 
        
            
               | 
               | 
              40 | 
              # If coming from acq we may be getting ratios for titles in a single basket  | 
            
            
              | 41 | 
              my $booksellerid    = $input->param('booksellerid') // ''; | 
            
            
              | 42 | 
              my $basketno        = $input->param('basketno') // ''; | 
            
            
              | 43 | 
              $debug and warn output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 }); | 
            
            
              | 44 | 
               | 
            
        
          | 41 | 
          my ( $template, $loggedinuser, $cookie ) = get_template_and_user(  | 
          45 | 
          my ( $template, $loggedinuser, $cookie ) = get_template_and_user(  | 
        
        
          | 42 | 
              { | 
          46 | 
              { | 
        
        
          | 43 | 
                  template_name   => "circ/reserveratios.tt",  | 
          47 | 
                  template_name   => "circ/reserveratios.tt",  | 
        
  
    | 
      
            Lines 49-218
          my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
      
      
        Link Here
      
     | 
  
        
          | 49 | 
              }  | 
          53 | 
              }  | 
        
        
          | 50 | 
          );  | 
          54 | 
          );  | 
        
        
          | 51 | 
           | 
          55 | 
           | 
        
          
            
              | 52 | 
              my $booksellerid = $input->param('booksellerid') // ''; | 
              56 | 
              my ($reservedata,$sql) = Koha::Util::HoldsRatios::get_ratios({ | 
            
            
              | 53 | 
              my $basketno = $input->param('basketno') // ''; | 
              57 | 
                  from            => $startdate,  | 
            
            
              | 54 | 
              if ($booksellerid && $basketno) { | 
              58 | 
                  to              => $enddate,  | 
            
            
              | 55 | 
                   $template->param( booksellerid => $booksellerid, basketno => $basketno );  | 
              59 | 
                  ratio           => $ratio,  | 
            
            
              | 56 | 
              }  | 
              60 | 
                  include_ordered => $include_ordered,  | 
            
            
              | 57 | 
               | 
              61 | 
                  basketno        => $basketno  | 
            
            
              | 58 | 
              my $effective_create_items = q{}; | 
              62 | 
              });  | 
            
            
              | 59 | 
              if ( $basketno ){ | 
               | 
               | 
            
            
              | 60 | 
                  my $basket = Koha::Acquisition::Baskets->find( $basketno );  | 
            
            
              | 61 | 
                  if ($basket){ | 
            
            
              | 62 | 
                      $effective_create_items = $basket->effective_create_items;  | 
            
            
              | 63 | 
                  } else { | 
            
            
              | 64 | 
                      $effective_create_items = C4::Context->preference('AcqCreateItem'); | 
            
            
              | 65 | 
                  }  | 
            
            
              | 66 | 
              }  | 
            
            
              | 67 | 
               | 
            
            
              | 68 | 
              $startdate = eval { dt_from_string( $startdate ) } if $startdate; | 
            
            
              | 69 | 
              $enddate = eval { dt_from_string( $enddate ) } if $enddate; | 
            
            
              | 70 | 
               | 
            
            
              | 71 | 
              my $todaysdate = dt_from_string;  | 
            
            
              | 72 | 
               | 
            
            
              | 73 | 
              #    A default of the prior years's holds is a reasonable way to pull holds  | 
            
            
              | 74 | 
              $enddate = $todaysdate unless $enddate;  | 
            
            
              | 75 | 
              $startdate = $todaysdate->clone->subtract( years => 1 ) unless $startdate;  | 
            
            
              | 76 | 
               | 
            
            
              | 77 | 
              if (!defined($ratio)) { | 
            
            
              | 78 | 
                  $ratio = 3;  | 
            
            
              | 79 | 
              }  | 
            
            
              | 80 | 
              # Force to be a number  | 
            
            
              | 81 | 
              $ratio += 0;  | 
            
            
              | 82 | 
              if ($ratio <= 0) { | 
            
            
              | 83 | 
                  $ratio = 1; # prevent division by zero  | 
            
            
              | 84 | 
              }  | 
            
            
              | 85 | 
               | 
            
            
              | 86 | 
              my $dbh    = C4::Context->dbh;  | 
            
            
              | 87 | 
              my $sqldatewhere = "";  | 
            
            
              | 88 | 
              $debug and warn output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 }); | 
            
            
              | 89 | 
              my @query_params = ();  | 
            
            
              | 90 | 
               | 
            
            
              | 91 | 
              $sqldatewhere .= " AND reservedate >= ?";  | 
            
            
              | 92 | 
              push @query_params, output_pref({ dt => $startdate, dateformat => 'iso' }) ; | 
            
            
              | 93 | 
              $sqldatewhere .= " AND reservedate <= ?";  | 
            
            
              | 94 | 
              push @query_params, output_pref({ dt => $enddate, dateformat => 'iso' }); | 
            
            
              | 95 | 
               | 
            
            
              | 96 | 
              my $include_aqorders_qty =  | 
            
            
              | 97 | 
                $effective_create_items eq 'receiving'  | 
            
            
              | 98 | 
                ? '+ COALESCE(aqorders.quantity, 0) - COALESCE(aqorders.quantityreceived, 0)'  | 
            
            
              | 99 | 
                : q{}; | 
            
            
              | 100 | 
               | 
            
            
              | 101 | 
              my $include_aqorders_qty_join =  | 
            
            
              | 102 | 
                $effective_create_items eq 'receiving'  | 
            
            
              | 103 | 
                ? 'LEFT JOIN aqorders ON reserves.biblionumber=aqorders.biblionumber'  | 
            
            
              | 104 | 
                : q{}; | 
            
            
              | 105 | 
               | 
            
            
              | 106 | 
              my $nfl_comparison = $include_ordered ? '<=' : '=';  | 
            
            
              | 107 | 
              my $strsth =  | 
            
            
              | 108 | 
              "SELECT reservedate,  | 
            
            
              | 109 | 
                      reserves.borrowernumber as borrowernumber,  | 
            
            
              | 110 | 
                      reserves.biblionumber,  | 
            
            
              | 111 | 
                      reserves.branchcode as branch,  | 
            
            
              | 112 | 
                      items.holdingbranch,  | 
            
            
              | 113 | 
                      items.itemcallnumber,  | 
            
            
              | 114 | 
                      items.itemnumber,  | 
            
            
              | 115 | 
                      GROUP_CONCAT(DISTINCT items.itemcallnumber   | 
            
            
              | 116 | 
                          ORDER BY items.itemnumber SEPARATOR '|') as listcall,  | 
            
            
              | 117 | 
                      GROUP_CONCAT(DISTINCT homebranch  | 
            
            
              | 118 | 
                          ORDER BY items.itemnumber SEPARATOR '|') as homebranch_list,  | 
            
            
              | 119 | 
                      GROUP_CONCAT(DISTINCT holdingbranch   | 
            
            
              | 120 | 
                          ORDER BY items.itemnumber SEPARATOR '|') as holdingbranch_list,  | 
            
            
              | 121 | 
                      GROUP_CONCAT(DISTINCT items.location   | 
            
            
              | 122 | 
                          ORDER BY items.itemnumber SEPARATOR '|') as l_location,  | 
            
            
              | 123 | 
                      GROUP_CONCAT(DISTINCT items.itype   | 
            
            
              | 124 | 
                          ORDER BY items.itemnumber SEPARATOR '|') as l_itype,  | 
            
            
              | 125 | 
               | 
            
            
              | 126 | 
                      reserves.found,  | 
            
            
              | 127 | 
                      biblio.title,  | 
            
            
              | 128 | 
                      biblio.author,  | 
            
            
              | 129 | 
                      count(DISTINCT reserves.borrowernumber) as reservecount,   | 
            
            
              | 130 | 
                      count(DISTINCT items.itemnumber) $include_aqorders_qty as itemcount  | 
            
            
              | 131 | 
               FROM  reserves  | 
            
            
              | 132 | 
               LEFT JOIN items ON items.biblionumber=reserves.biblionumber   | 
            
            
              | 133 | 
               LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber  | 
            
            
              | 134 | 
               $include_aqorders_qty_join  | 
            
            
              | 135 | 
               WHERE  | 
            
            
              | 136 | 
               notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0  | 
            
            
              | 137 | 
               $sqldatewhere  | 
            
            
              | 138 | 
              ";  | 
            
            
              | 139 | 
               | 
            
            
              | 140 | 
              if (C4::Context->preference('IndependentBranches')){ | 
            
            
              | 141 | 
                  $strsth .= " AND items.holdingbranch=? ";  | 
            
            
              | 142 | 
                  push @query_params, C4::Context->userenv->{'branch'}; | 
            
            
              | 143 | 
              }  | 
            
            
              | 144 | 
               | 
            
            
              | 145 | 
              $strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC";  | 
            
            
              | 146 | 
               | 
            
            
              | 147 | 
              $template->param(sql => $strsth);  | 
            
            
              | 148 | 
              my $sth = $dbh->prepare($strsth);  | 
            
            
              | 149 | 
              $sth->execute(@query_params);  | 
            
            
              | 150 | 
               | 
            
            
              | 151 | 
              my $ratio_atleast1 = ($ratio >= 1) ? 1 : 0;  | 
            
            
              | 152 | 
              my @reservedata;  | 
            
            
              | 153 | 
              while ( my $data = $sth->fetchrow_hashref ) { | 
            
            
              | 154 | 
                  my $thisratio = $data->{reservecount} / $data->{itemcount}; | 
            
            
              | 155 | 
                  my $ratiocalc = ($thisratio / $ratio);  | 
            
            
              | 156 | 
                  ($thisratio / $ratio) >= 1 or next;  # TODO: tighter targeting -- get ratio limit into SQL using HAVING clause  | 
            
            
              | 157 | 
                  my $record = GetMarcBiblio({ biblionumber => $data->{biblionumber} }); | 
            
            
              | 158 | 
                  $data->{subtitle} = GetRecordValue('subtitle', $record, GetFrameworkCode($data->{biblionumber})); | 
            
            
              | 159 | 
                  push(  | 
            
            
              | 160 | 
                      @reservedata,  | 
            
            
              | 161 | 
                      { | 
            
            
              | 162 | 
                          reservedate        => $data->{reservedate}, | 
            
            
              | 163 | 
                          priority           => $data->{priority}, | 
            
            
              | 164 | 
                          name               => $data->{borrower}, | 
            
            
              | 165 | 
                          title              => $data->{title}, | 
            
            
              | 166 | 
                          subtitle           => $data->{subtitle}, | 
            
            
              | 167 | 
                          author             => $data->{author}, | 
            
            
              | 168 | 
                          itemnum            => $data->{itemnumber}, | 
            
            
              | 169 | 
                          biblionumber       => $data->{biblionumber}, | 
            
            
              | 170 | 
                          holdingbranch      => $data->{holdingbranch}, | 
            
            
              | 171 | 
                          homebranch_list    => [split('\|', $data->{homebranch_list})], | 
            
            
              | 172 | 
                          holdingbranch_list => [split('\|', $data->{holdingbranch_list})], | 
            
            
              | 173 | 
                          branch             => $data->{branch}, | 
            
            
              | 174 | 
                          itemcallnumber     => $data->{itemcallnumber}, | 
            
            
              | 175 | 
                          location           => [split('\|', $data->{l_location})], | 
            
            
              | 176 | 
                          itype              => [split('\|', $data->{l_itype})], | 
            
            
              | 177 | 
                          reservecount       => $data->{reservecount}, | 
            
            
              | 178 | 
                          itemcount          => $data->{itemcount}, | 
            
            
              | 179 | 
                          ratiocalc          => sprintf( "%.0d", $ratio_atleast1 ? ( $thisratio / $ratio ) : $thisratio ),  | 
            
            
              | 180 | 
                          thisratio => sprintf( "%.2f", $thisratio ),  | 
            
            
              | 181 | 
                          thisratio_atleast1 => ( $thisratio >= 1 ) ? 1 : 0,  | 
            
            
              | 182 | 
                          listcall           => [split('\|', $data->{listcall})] | 
            
            
              | 183 | 
                      }  | 
            
            
              | 184 | 
                  );  | 
            
            
              | 185 | 
              }  | 
            
            
              | 186 | 
               | 
            
            
              | 187 | 
              for my $rd ( @reservedata ) { | 
            
            
              | 188 | 
                  next unless $rd->{biblionumber}; | 
            
            
              | 189 | 
                  $rd->{pendingorders} = CountPendingOrdersByBiblionumber( $rd->{biblionumber} ); | 
            
            
              | 190 | 
              }  | 
            
        
          | 191 | 
           | 
          63 | 
           | 
        
        
          | 192 | 
          $template->param(  | 
          64 | 
          $template->param(  | 
        
          
            
              | 193 | 
                  ratio_atleast1  => $ratio_atleast1,  | 
              65 | 
                  todaysdate      => dt_from_string,  | 
            
            
              | 194 | 
                  todaysdate      => $todaysdate,  | 
               | 
               | 
            
        
          | 195 | 
              from            => $startdate,  | 
          66 | 
              from            => $startdate,  | 
        
        
          | 196 | 
              to              => $enddate,  | 
          67 | 
              to              => $enddate,  | 
        
        
          | 197 | 
              ratio           => $ratio,  | 
          68 | 
              ratio           => $ratio,  | 
        
        
          | 198 | 
              include_ordered => $include_ordered,  | 
          69 | 
              include_ordered => $include_ordered,  | 
        
          
            
              | 199 | 
                  reserveloop     => \@reservedata,  | 
              70 | 
                  reserveloop     => $reservedata,  | 
            
        
          | 200 | 
          );  | 
          71 | 
          );  | 
        
            
               | 
               | 
              72 | 
              if ($booksellerid && $basketno) { | 
            
            
              | 73 | 
                  $template->param(   | 
            
            
              | 74 | 
                      booksellerid => $booksellerid,   | 
            
            
              | 75 | 
                      basketno => $basketno   | 
            
            
              | 76 | 
                  );  | 
            
            
              | 77 | 
              }  | 
            
        
          | 201 | 
           | 
          78 | 
           | 
        
        
          | 202 | 
          output_html_with_http_headers $input, $cookie, $template->output;  | 
          79 | 
          output_html_with_http_headers $input, $cookie, $template->output;  | 
        
        
          | 203 | 
           | 
          80 | 
           | 
        
            
              | 204 | 
              sub CountPendingOrdersByBiblionumber { | 
               | 
               | 
            
            
              | 205 | 
                  my $biblionumber = shift;  | 
            
            
              | 206 | 
                  my @orders = GetOrdersByBiblionumber( $biblionumber );  | 
            
            
              | 207 | 
                  my $cnt = 0;  | 
            
            
              | 208 | 
                  if (scalar(@orders)) { | 
            
            
              | 209 | 
                      for my $order ( @orders ) { | 
            
            
              | 210 | 
                          next if $order->{datecancellationprinted}; | 
            
            
              | 211 | 
                          my $onum = $order->{quantity} // 0; | 
            
            
              | 212 | 
                          my $rnum = $order->{quantityreceived} // 0; | 
            
            
              | 213 | 
                          next if $rnum >= $onum;  | 
            
            
              | 214 | 
                          $cnt += ($onum - $rnum);  | 
            
            
              | 215 | 
                      }  | 
            
            
              | 216 | 
                  }  | 
            
            
              | 217 | 
                  return $cnt;  | 
            
            
              | 218 | 
              }  | 
            
            
              | 219 | 
              -   |