Attachment #26681 for bug #7683

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




[% ELSE %]

	<form method="post" action="/cgi-bin/koha/reports/catalogue_stats.pl">
	<input type="hidden" name="Filter" /> <input type="hidden" name="Filter" />
	[% UNLESS ( haslccn ) %]
	<input type="hidden" name="Filter" /> <input type="hidden" name="Filter" />
	[% END %]
	<fieldset class="rows">
	<legend>Catalog statistics</legend>
	<table>

			</tr>
		</thead>
		<tbody>
      <tr>
        <td>Koha full call number</td>
        <td><input type="radio" name="Line" value="items.itemcallnumber" /></td>
        <td><input type="radio" name="Column" value="items.itemcallnumber" /></td>
        <td><label for="callnoFrom">From</label> <input type="text" name="Filter" id="callnoFrom" /> <label for="callnoTo">To</label> <input type="text" name="Filter" id="callnoTo" /></td>
      </tr>
     <tr>
        <td>&nbsp;</td>
        <td colspan="2"><select name="cotedigits" id="cotedigits">
          <option value=""> </option>
          <option value ="1">1</option>
          <option value ="2">2</option>
          <option value ="3">3</option>
          <option value ="4">4</option>
          <option value ="5">5</option>
          </select>
          <label for="cotedigits">characters</label> 
        </td>
        <td>&nbsp;</td>
      </tr>
				</tr>
			[% END %]
			[% IF ( hascote ) %]
			  <tr>
                    <td>Koha full call number</td>
					<td><input type="radio" name="Line" value="items.itemcallnumber" /></td>
					<td><input type="radio" name="Column" value="items.itemcallnumber" /></td>
                    <td><label for="callnoFrom">From</label> <input type="text" name="Filter" id="callnoFrom" /> <label for="callnoTo">To</label> <input type="text" name="Filter" id="callnoTo" /></td>
				</tr>
			 <tr>
					<td>&nbsp;</td>
					<td colspan="2"><select name="cotedigits" id="cotedigits">
						<option value=""> </option>
						<option value ="1">1</option>
						<option value ="2">2</option>
						<option value ="3">3</option>
						<option value ="4">4</option>
						<option value ="5">5</option>
						</select>
						<label for="cotedigits">characters</label> 
					</td>
					<td>&nbsp;</td>
				</tr>
				[% ELSE %]
				<tr><td colspan="4"><input type="hidden" name="Filter" /> <input type="hidden" name="Filter" /></td></tr>
			[% END %]
			 <tr>
                <td>Item type</td>
				<td><input type="radio" name="Line" value="[% item_itype %]" /></td>




my $column      = $input->param("Column");
my $cellvalue      = $input->param("Cellvalue"); # one of 'items', 'biblios', 'deleteditems'
my @filters     = $input->param("Filter");
my $deweydigits = $input->param("deweydigits");
my $lccndigits  = $input->param("lccndigits");
my $cotedigits  = $input->param("cotedigits");
my $output      = $input->param("output");
my $basename    = $input->param("basename");

				});
$template->param(do_it => $do_it);
if ($do_it) {
    my $results = calculate( $line, $column, $cellvalue, $cotedigits, \@filters );
    if ( $output eq "screen" ) {
        $template->param( mainloop => $results );
        output_html_with_http_headers $input, $cookie, $template->output;

	my $count=0;
	my $req;
	my @select;
	# FIXME: no such field "dewey"
	# $req = $dbh->prepare("select count(dewey) from biblioitems ");
	# $req->execute;
	my $hasdewey = 0;

# (rch) biblioitems.lccn is mapped to lccn MARC21 010$a in default framework.
# This is not the LC Classification.  It's the Control Number.
# So I'm just going to remove this bit.  Call Number is handled in itemcallnumber.
#
	my $haslccn = 0;
#	$req = $dbh->prepare( "select count(lccn) from biblioitems ");
#	$req->execute;
#	my $hlghtlccn;
#	while (my ($value) =$req->fetchrow) {
#		$hlghtlccn = !($hasdewey);
#		$haslccn =1 if (($value>2) and (! $haslccn));
#		$count++ if (($value) and (! $haslccn));
#		push @select, $value;
#	}
# 	my $CGIlccn=CGI::scrolling_list( -name     => 'Filter',
# 				-id => 'Filter',
# 				-values   => \@select,
# 				-size     => 1,
# 				-multiple => 0 );

# No need to test for data here.  If you don't have itemcallnumbers, you probably know it.
# FIXME: Hardcoding to 5 chars on itemcallnum. 
#
    my $hascote = 1;
    my $highcote = 5;

    $req = $dbh->prepare("select itemtype, description from itemtypes order by description");
    $req->execute;

    my @mime  = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation

    $template->param(
        hasdewey     => $hasdewey,
        haslccn      => $haslccn,
        hascote      => $hascote,
        CGIItemType  => $CGIitemtype,
        CGIBranch    => GetBranchesLoop( C4::Context->userenv->{'branch'} ),
        locationloop => \@locations,



sub calculate {
    my ( $line, $column, $cellvalue, $cotedigits, $filters ) = @_;
    my @mainloop;
    my @loopfooter;
    my @loopcol;

    my @loopfilter;
    for ( my $i = 0 ; $i <= @$filters ; $i++ ) {
        my %cell;
        if ( defined @$filters[$i] and @$filters[$i] ne '' and $i != 11 ) {
            if ( ( ( $i == 1 ) or ( $i == 5 ) ) and ( @$filters[ $i - 1 ] ) ) {
                $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
            }
            $cell{filter} .= @$filters[$i];
            $cell{crit} .=
                ( $i == 0 )  ? "Item CallNumber From"
              : ( $i == 1 )  ? "Item CallNumber To"
              : ( $i == 2 )  ? "Item type"
              : ( $i == 3 )  ? "Publisher"
              : ( $i == 4 )  ? "Publication year From"
              : ( $i == 5 )  ? "Publication year To"
              : ( $i == 6 ) ? "Library"
              : ( $i == 7 ) ? "Shelving Location"
              : ( $i == 8 ) ? "Collection Code"
              : ( $i == 9 ) ? "Status"
              : ( $i == 10 ) ? "Materials"
              : ( $i == 12 and $filters->[11] == 0 ) ? "Barcode (not like)"
              : ( $i == 12 and $filters->[11] == 1 ) ? "Barcode (like)"
              : ( $i == 13 ) ? "Acquired date from"
              : ( $i == 14 ) ? "Acquired date to"
              : ( $i == 15 ) ? "Removed date from"
              : ( $i == 16 ) ? "Removed date to"
              : ( $i == 17 ) ? "Acquired date from"
              : ( $i == 18 ) ? "Acquired date to"
              : ( $i == 19 ) ? "Removed date from"
              : ( $i == 20 ) ? "Removed date to"
              :                '';

            push @loopfilter, \%cell;
        }
    }

    @$filters[14] = C4::Dates->new(@$filters[14])->output('iso') if @$filters[14];
    @$filters[15] = C4::Dates->new(@$filters[15])->output('iso') if @$filters[15];
    @$filters[16] = C4::Dates->new(@$filters[16])->output('iso') if @$filters[16];
    @$filters[17] = C4::Dates->new(@$filters[17])->output('iso') if @$filters[17];

    my @linefilter;
    $linefilter[0] = @$filters[0] if ( $line =~ /items\.itemcallnumber/ );
    $linefilter[1] = @$filters[1] if ( $line =~ /items\.itemcallnumber/ );
    $linefilter[0] = @$filters[2] if ( $line =~ /lccn/ );
    $linefilter[1] = @$filters[3] if ( $line =~ /lccn/ );
    $linefilter[0] = @$filters[4] if ( $line =~ /items\.itemcallnumber/ );
    $linefilter[1] = @$filters[5] if ( $line =~ /items\.itemcallnumber/ );
    if ( C4::Context->preference('item-level_itypes') ) {
        $linefilter[0] = @$filters[2] if ( $line =~ /items\.itype/ );
    } else {
        $linefilter[0] = @$filters[2] if ( $line =~ /itemtype/ );
    }
    $linefilter[0] = @$filters[3] if ( $line =~ /publishercode/ );
    $linefilter[0] = @$filters[4] if ( $line =~ /publicationyear/ );
    $linefilter[1] = @$filters[5] if ( $line =~ /publicationyear/ );

    $linefilter[0] = @$filters[6] if ( $line =~ /items\.homebranch/ );
    $linefilter[0] = @$filters[7] if ( $line =~ /items\.location/ );
    $linefilter[0] = @$filters[8] if ( $line =~ /items\.ccode/ );
    $linefilter[0] = @$filters[9] if ( $line =~ /items\.notforloan/ );
    $linefilter[0] = @$filters[10] if ( $line =~ /items\.materials/ );
    $linefilter[0] = @$filters[13] if ( $line =~ /items\.dateaccessioned/ );
    $linefilter[1] = @$filters[14] if ( $line =~ /items\.dateaccessioned/ );
    $linefilter[0] = @$filters[15] if ( $line =~ /deleteditems\.timestamp/ );
    $linefilter[1] = @$filters[16] if ( $line =~ /deleteditems\.timestamp/ );

    my @colfilter;
    $colfilter[0] = @$filters[0] if ( $column =~ /items\.itemcallnumber/ );
    $colfilter[1] = @$filters[1] if ( $column =~ /items\.itemcallnumber/ );
    $colfilter[0] = @$filters[2] if ( $column =~ /lccn/ );
    $colfilter[1] = @$filters[3] if ( $column =~ /lccn/ );
    $colfilter[0] = @$filters[4] if ( $column =~ /items\.itemcallnumber/ );
    $colfilter[1] = @$filters[5] if ( $column =~ /items\.itemcallnumber/ );
    if ( C4::Context->preference('item-level_itypes') ) {
        $colfilter[0] = @$filters[2] if ( $column =~ /items\.itype/ );
    } else {
        $colfilter[0] = @$filters[2] if ( $column =~ /itemtype/ );
    }
    $colfilter[0] = @$filters[3]  if ( $column =~ /publishercode/ );
    $colfilter[0] = @$filters[4]  if ( $column =~ /publicationyear/ );
    $colfilter[1] = @$filters[5]  if ( $column =~ /publicationyear/ );
    $colfilter[0] = @$filters[6] if ( $column =~ /items\.homebranch/ );
    $colfilter[0] = @$filters[7] if ( $column =~ /items\.location/ );
    $colfilter[0] = @$filters[8] if ( $column =~ /items\.ccode/ );
    $colfilter[0] = @$filters[9] if ( $column =~ /items\.notforloan/ );
    $colfilter[0] = @$filters[10] if ( $column =~ /items\.materials/ );
    $colfilter[0] = @$filters[13] if ( $column =~ /items.dateaccessioned/ );
    $colfilter[1] = @$filters[14] if ( $column =~ /items\.dateaccessioned/ );
    $colfilter[0] = @$filters[15] if ( $column =~ /deleteditems\.timestamp/ );
    $colfilter[1] = @$filters[16] if ( $column =~ /deleteditems\.timestamp/ );

    # 1st, loop rows.
    my $origline = $line;
    $line =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
    my $linefield;
    if ( ( $line =~ /itemcallnumber/ ) and ($cotedigits) ) {
        $linefield = "left($line,$deweydigits)";
    } elsif ( ( $line =~ /lccn/ ) and ($lccndigits) ) {
        $linefield = "left($line,$lccndigits)";
    } elsif ( ( $line =~ /itemcallnumber/ ) and ($cotedigits) ) {
        $linefield = "left($line,$cotedigits)";
    } elsif ( $line =~ /^deleteditems\.timestamp$/ ) {
        $linefield = "DATE($line)";

    my $origcolumn = $column;
    $column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
    my $colfield;
    if ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {
        $colfield = "left($column,$deweydigits)";
    } elsif ( ( $column =~ /lccn/ ) and ($lccndigits) ) {
        $colfield = "left($column,$lccndigits)";
    } elsif ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {
        $colfield = "left($column,$cotedigits)";
    } elsif ( $column =~ /^deleteditems\.timestamp$/ ) {
        $colfield = "DATE($column)";


    if ( @$filters[0] ) {
        @$filters[0] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[0] );
    }

    if ( @$filters[1] ) {
        @$filters[1] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[1] );
    }

    if ( @$filters[2] ) {
        @$filters[2] =~ s/\*/%/g;
        $strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[2] . "'";
    }

    if ( @$filters[3] ) {
        @$filters[3] =~ s/\*/%/g;
        @$filters[3] .= "%" unless @$filters[3] =~ /%/;
        $strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[3] . "\"";
    }
    if ( @$filters[4] ) {
        @$filters[4] =~ s/\*/%/g;
        $strcalc .= " AND publicationyear >" . @$filters[4];
    }

    if ( @$filters[5] ) {
        @$filters[5] =~ s/\*/%/g;
        $strcalc .= " AND publicationyear <" . @$filters[5];
    }

    if ( @$filters[6] ) {
        @$filters[6] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[6] . "'";
    }

    if ( @$filters[7] ) {
        @$filters[7] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.location LIKE '" . @$filters[7] . "'";
        $strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] . "\"";
    }
    if ( @$filters[8] ) {
        @$filters[8] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.ccode  LIKE '" . @$filters[8] . "'";
    }
    if ( defined @$filters[9] and @$filters[9] ne '' ) {
        @$filters[9] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.notforloan  LIKE '" . @$filters[13] . "'";
    }
    if ( defined @$filters[10] and @$filters[10] ne '' ) {
        @$filters[10] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.materials  LIKE '" . @$filters[10] . "'";
    }
    if ( @$filters[11] ) {
        @$filters[11] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.location LIKE '" . @$filters[11] . "'";
    }
    if ( @$filters[12] ) {
        @$filters[12] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.ccode  LIKE '" . @$filters[12] . "'";
    }
    if ( @$filters[13] ) {
        @$filters[13] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[13]' ";
    }
    if ( @$filters[14] ) {
        @$filters[14] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[14]' ";
    }
    if ( @$filters[17] ) {
        @$filters[17] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[17]' ";
    }
    if ( @$filters[18] ) {
        @$filters[18] =~ s/\*/%/g;
        $strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[18]' ";
    }
    if ( $cellvalue eq 'deleteditems' and @$filters[15] ) {
        @$filters[15] =~ s/\*/%/g;
        $strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[15]' ";
    }
    if ( $cellvalue eq 'deleteditems' and @$filters[16] ) {
        @$filters[16] =~ s/\*/%/g;
        $strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[16]' ";
    }
    $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
    $debug and warn "SQL: $strcalc";
- 

Return to bug 7683

Lines 101-110 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tt (-50 / +20 lines)
101	[% ELSE %]	101	[% ELSE %]
102		102
103	<form method="post" action="/cgi-bin/koha/reports/catalogue_stats.pl">	103	<form method="post" action="/cgi-bin/koha/reports/catalogue_stats.pl">
104	<input type="hidden" name="Filter" /> <input type="hidden" name="Filter" />
105	[% UNLESS ( haslccn ) %]
106	<input type="hidden" name="Filter" /> <input type="hidden" name="Filter" />
107	[% END %]
108	<fieldset class="rows">	104	<fieldset class="rows">
109	<legend>Catalog statistics</legend>	105	<legend>Catalog statistics</legend>
110	<table>	106	<table>
Lines 117-168 Link Here
117	</tr>	113	</tr>
118	</thead>	114	</thead>
119	<tbody>	115	<tbody>
120	[% IF ( haslccn ) %]	116	<tr>
121	<tr>	117	<td>Koha full call number</td>
122	<td>LoC classification</td>	118	<td><input type="radio" name="Line" value="items.itemcallnumber" /></td>
123	<td><input type="radio" name="Line" value="lccn" /></td>	119	<td><input type="radio" name="Column" value="items.itemcallnumber" /></td>
124	<td><input type="radio" name="Column" value="lccn" /></td>	120	<td><label for="callnoFrom">From</label> <input type="text" name="Filter" id="callnoFrom" /> <label for="callnoTo">To</label> <input type="text" name="Filter" id="callnoTo" /></td>
125	<td> <label for="locFrom">From</label> <input type="text" name="Filter" id="locFrom" /> <label for="locTo">To</label> <input type="text" name="Filter" id="locTo" /></td>	121	</tr>
126	</tr>	122	<tr>
127	<tr>	123	<td> </td>
128	<td> </td>	124	<td colspan="2"><select name="cotedigits" id="cotedigits">
129	<td colspan="2"><select name="lccndigits" id="lccndigits">	125	<option value=""> </option>
130	<option value=""> </option>	126	<option value ="1">1</option>
131	<option value ="1">1</option>	127	<option value ="2">2</option>
132	<option value ="2">2</option>	128	<option value ="3">3</option>
133	<option value ="3">3</option>	129	<option value ="4">4</option>
134	<option value ="4">4</option>	130	<option value ="5">5</option>
135	<option value ="5">5</option>	131	</select>
136	</select>	132	<label for="cotedigits">characters</label>
137	<label for="lccndigits">characters</label>	133	</td>
138	</td>	134	<td> </td>
139	<td> </td>	135	</tr>
140	</tr>
141	[% END %]
142	[% IF ( hascote ) %]
143	<tr>
144	<td>Koha full call number</td>
145	<td><input type="radio" name="Line" value="items.itemcallnumber" /></td>
146	<td><input type="radio" name="Column" value="items.itemcallnumber" /></td>
147	<td><label for="callnoFrom">From</label> <input type="text" name="Filter" id="callnoFrom" /> <label for="callnoTo">To</label> <input type="text" name="Filter" id="callnoTo" /></td>
148	</tr>
149	<tr>
150	<td> </td>
151	<td colspan="2"><select name="cotedigits" id="cotedigits">
152	<option value=""> </option>
153	<option value ="1">1</option>
154	<option value ="2">2</option>
155	<option value ="3">3</option>
156	<option value ="4">4</option>
157	<option value ="5">5</option>
158	</select>
159	<label for="cotedigits">characters</label>
160	</td>
161	<td> </td>
162	</tr>
163	[% ELSE %]
164	<tr><td colspan="4"><input type="hidden" name="Filter" /> <input type="hidden" name="Filter" /></td></tr>
165	[% END %]
166	<tr>	136	<tr>
167	<td>Item type</td>	137	<td>Item type</td>
168	<td><input type="radio" name="Line" value="[% item_itype %]" /></td>	138	<td><input type="radio" name="Line" value="[% item_itype %]" /></td>

Lines 46-53 my $line = $input->param("Line"); Link Here

(-)a/reports/catalogue_stats.pl (-161 / +89 lines)
46	my $column = $input->param("Column");	46	my $column = $input->param("Column");
47	my $cellvalue = $input->param("Cellvalue"); # one of 'items', 'biblios', 'deleteditems'	47	my $cellvalue = $input->param("Cellvalue"); # one of 'items', 'biblios', 'deleteditems'
48	my @filters = $input->param("Filter");	48	my @filters = $input->param("Filter");
49	my $deweydigits = $input->param("deweydigits");
50	my $lccndigits = $input->param("lccndigits");
51	my $cotedigits = $input->param("cotedigits");	49	my $cotedigits = $input->param("cotedigits");
52	my $output = $input->param("output");	50	my $output = $input->param("output");
53	my $basename = $input->param("basename");	51	my $basename = $input->param("basename");
Lines 70-76 my ($template, $borrowernumber, $cookie) Link Here
70	});	68	});
71	$template->param(do_it => $do_it);	69	$template->param(do_it => $do_it);
72	if ($do_it) {	70	if ($do_it) {
73	my $results = calculate( $line, $column, $cellvalue, $deweydigits, $lccndigits, $cotedigits, \@filters );	71	my $results = calculate( $line, $column, $cellvalue, $cotedigits, \@filters );
74	if ( $output eq "screen" ) {	72	if ( $output eq "screen" ) {
75	$template->param( mainloop => $results );	73	$template->param( mainloop => $results );
76	output_html_with_http_headers $input, $cookie, $template->output;	74	output_html_with_http_headers $input, $cookie, $template->output;
Lines 113-148 if ($do_it) { Link Here
113	my $count=0;	111	my $count=0;
114	my $req;	112	my $req;
115	my @select;	113	my @select;
116	# FIXME: no such field "dewey"
117	# $req = $dbh->prepare("select count(dewey) from biblioitems ");
118	# $req->execute;
119	my $hasdewey = 0;
120
121	# (rch) biblioitems.lccn is mapped to lccn MARC21 010$a in default framework.
122	# This is not the LC Classification. It's the Control Number.
123	# So I'm just going to remove this bit. Call Number is handled in itemcallnumber.
124	#
125	my $haslccn = 0;
126	# $req = $dbh->prepare( "select count(lccn) from biblioitems ");
127	# $req->execute;
128	# my $hlghtlccn;
129	# while (my ($value) =$req->fetchrow) {
130	# $hlghtlccn = !($hasdewey);
131	# $haslccn =1 if (($value>2) and (! $haslccn));
132	# $count++ if (($value) and (! $haslccn));
133	# push @select, $value;
134	# }
135	# my $CGIlccn=CGI::scrolling_list( -name => 'Filter',
136	# -id => 'Filter',
137	# -values => \@select,
138	# -size => 1,
139	# -multiple => 0 );
140
141	# No need to test for data here. If you don't have itemcallnumbers, you probably know it.
142	# FIXME: Hardcoding to 5 chars on itemcallnum.
143	#
144	my $hascote = 1;
145	my $highcote = 5;
146		114
147	$req = $dbh->prepare("select itemtype, description from itemtypes order by description");	115	$req = $dbh->prepare("select itemtype, description from itemtypes order by description");
148	$req->execute;	116	$req->execute;
Lines 181-189 if ($do_it) { Link Here
181	my @mime = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation	149	my @mime = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation
182		150
183	$template->param(	151	$template->param(
184	hasdewey => $hasdewey,
185	haslccn => $haslccn,
186	hascote => $hascote,
187	CGIItemType => $CGIitemtype,	152	CGIItemType => $CGIitemtype,
188	CGIBranch => GetBranchesLoop( C4::Context->userenv->{'branch'} ),	153	CGIBranch => GetBranchesLoop( C4::Context->userenv->{'branch'} ),
189	locationloop => \@locations,	154	locationloop => \@locations,
Lines 200-206 output_html_with_http_headers $input, $cookie, $template->output; Link Here
200		165
201		166
202	sub calculate {	167	sub calculate {
203	my ( $line, $column, $cellvalue, $deweydigits, $lccndigits, $cotedigits, $filters ) = @_;	168	my ( $line, $column, $cellvalue, $cotedigits, $filters ) = @_;
204	my @mainloop;	169	my @mainloop;
205	my @loopfooter;	170	my @loopfooter;
206	my @loopcol;	171	my @loopcol;
Lines 233-336 sub calculate { Link Here
233	my @loopfilter;	198	my @loopfilter;
234	for ( my $i = 0 ; $i <= @$filters ; $i++ ) {	199	for ( my $i = 0 ; $i <= @$filters ; $i++ ) {
235	my %cell;	200	my %cell;
236	if ( defined @$filters[$i] and @$filters[$i] ne '' and $i != 15 ) {	201	if ( defined @$filters[$i] and @$filters[$i] ne '' and $i != 11 ) {
237	if ( ( ( $i == 1 ) or ( $i == 3 ) or ( $i == 5 ) or ( $i == 9 ) ) and ( @$filters[ $i - 1 ] ) ) {	202	if ( ( ( $i == 1 ) or ( $i == 5 ) ) and ( @$filters[ $i - 1 ] ) ) {
238	$cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );	203	$cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
239	}	204	}
240	$cell{filter} .= @$filters[$i];	205	$cell{filter} .= @$filters[$i];
241	$cell{crit} .=	206	$cell{crit} .=
242	( $i == 0 ) ? "Dewey Classification From"	207	( $i == 0 ) ? "Item CallNumber From"
243	: ( $i == 1 ) ? "Dewey Classification To"	208	: ( $i == 1 ) ? "Item CallNumber To"
244	: ( $i == 2 ) ? "Lccn Classification From"	209	: ( $i == 2 ) ? "Item type"
245	: ( $i == 3 ) ? "Lccn Classification To"	210	: ( $i == 3 ) ? "Publisher"
246	: ( $i == 4 ) ? "Item CallNumber From"	211	: ( $i == 4 ) ? "Publication year From"
247	: ( $i == 5 ) ? "Item CallNumber To"	212	: ( $i == 5 ) ? "Publication year To"
248	: ( $i == 6 ) ? "Item type"	213	: ( $i == 6 ) ? "Library"
249	: ( $i == 7 ) ? "Publisher"	214	: ( $i == 7 ) ? "Shelving Location"
250	: ( $i == 8 ) ? "Publication year From"	215	: ( $i == 8 ) ? "Collection Code"
251	: ( $i == 9 ) ? "Publication year To"	216	: ( $i == 9 ) ? "Status"
252	: ( $i == 10 ) ? "Library"	217	: ( $i == 10 ) ? "Materials"
253	: ( $i == 11 ) ? "Shelving Location"	218	: ( $i == 12 and $filters->[11] == 0 ) ? "Barcode (not like)"
254	: ( $i == 12 ) ? "Collection Code"	219	: ( $i == 12 and $filters->[11] == 1 ) ? "Barcode (like)"
255	: ( $i == 13 ) ? "Status"	220	: ( $i == 13 ) ? "Acquired date from"
256	: ( $i == 14 ) ? "Materials"	221	: ( $i == 14 ) ? "Acquired date to"
257	: ( $i == 16 and $filters->[15] == 0 ) ? "Barcode (not like)"	222	: ( $i == 15 ) ? "Removed date from"
258	: ( $i == 16 and $filters->[15] == 1 ) ? "Barcode (like)"	223	: ( $i == 16 ) ? "Removed date to"
259	: ( $i == 17 ) ? "Acquired date from"
260	: ( $i == 18 ) ? "Acquired date to"
261	: ( $i == 19 ) ? "Removed date from"
262	: ( $i == 20 ) ? "Removed date to"
263	: '';	224	: '';
264		225
265	push @loopfilter, \%cell;	226	push @loopfilter, \%cell;
266	}	227	}
267	}	228	}
268		229
269	@$filters[18] = C4::Dates->new(@$filters[18])->output('iso') if @$filters[18];	230	@$filters[14] = C4::Dates->new(@$filters[14])->output('iso') if @$filters[14];
270	@$filters[19] = C4::Dates->new(@$filters[19])->output('iso') if @$filters[19];	231	@$filters[15] = C4::Dates->new(@$filters[15])->output('iso') if @$filters[15];
271	@$filters[20] = C4::Dates->new(@$filters[20])->output('iso') if @$filters[20];	232	@$filters[16] = C4::Dates->new(@$filters[16])->output('iso') if @$filters[16];
272	@$filters[21] = C4::Dates->new(@$filters[21])->output('iso') if @$filters[21];	233	@$filters[17] = C4::Dates->new(@$filters[17])->output('iso') if @$filters[17];
273		234
274	my @linefilter;	235	my @linefilter;
275	$linefilter[0] = @$filters[0] if ( $line =~ /dewey/ );	236	$linefilter[0] = @$filters[0] if ( $line =~ /items\.itemcallnumber/ );
276	$linefilter[1] = @$filters[1] if ( $line =~ /dewey/ );	237	$linefilter[1] = @$filters[1] if ( $line =~ /items\.itemcallnumber/ );
277	$linefilter[0] = @$filters[2] if ( $line =~ /lccn/ );
278	$linefilter[1] = @$filters[3] if ( $line =~ /lccn/ );
279	$linefilter[0] = @$filters[4] if ( $line =~ /items\.itemcallnumber/ );
280	$linefilter[1] = @$filters[5] if ( $line =~ /items\.itemcallnumber/ );
281	if ( C4::Context->preference('item-level_itypes') ) {	238	if ( C4::Context->preference('item-level_itypes') ) {
282	$linefilter[0] = @$filters[6] if ( $line =~ /items\.itype/ );	239	$linefilter[0] = @$filters[2] if ( $line =~ /items\.itype/ );
283	} else {	240	} else {
284	$linefilter[0] = @$filters[6] if ( $line =~ /itemtype/ );	241	$linefilter[0] = @$filters[2] if ( $line =~ /itemtype/ );
285	}	242	}
286	$linefilter[0] = @$filters[7] if ( $line =~ /publishercode/ );	243	$linefilter[0] = @$filters[3] if ( $line =~ /publishercode/ );
287	$linefilter[0] = @$filters[8] if ( $line =~ /publicationyear/ );	244	$linefilter[0] = @$filters[4] if ( $line =~ /publicationyear/ );
288	$linefilter[1] = @$filters[9] if ( $line =~ /publicationyear/ );	245	$linefilter[1] = @$filters[5] if ( $line =~ /publicationyear/ );
289		246
290	$linefilter[0] = @$filters[10] if ( $line =~ /items\.homebranch/ );	247	$linefilter[0] = @$filters[6] if ( $line =~ /items\.homebranch/ );
291	$linefilter[0] = @$filters[11] if ( $line =~ /items\.location/ );	248	$linefilter[0] = @$filters[7] if ( $line =~ /items\.location/ );
292	$linefilter[0] = @$filters[12] if ( $line =~ /items\.ccode/ );	249	$linefilter[0] = @$filters[8] if ( $line =~ /items\.ccode/ );
293	$linefilter[0] = @$filters[13] if ( $line =~ /items\.notforloan/ );	250	$linefilter[0] = @$filters[9] if ( $line =~ /items\.notforloan/ );
294	$linefilter[0] = @$filters[14] if ( $line =~ /items\.materials/ );	251	$linefilter[0] = @$filters[10] if ( $line =~ /items\.materials/ );
295	$linefilter[0] = @$filters[17] if ( $line =~ /items\.dateaccessioned/ );	252	$linefilter[0] = @$filters[13] if ( $line =~ /items\.dateaccessioned/ );
296	$linefilter[1] = @$filters[18] if ( $line =~ /items\.dateaccessioned/ );	253	$linefilter[1] = @$filters[14] if ( $line =~ /items\.dateaccessioned/ );
297	$linefilter[0] = @$filters[19] if ( $line =~ /deleteditems\.timestamp/ );	254	$linefilter[0] = @$filters[15] if ( $line =~ /deleteditems\.timestamp/ );
298	$linefilter[1] = @$filters[20] if ( $line =~ /deleteditems\.timestamp/ );	255	$linefilter[1] = @$filters[16] if ( $line =~ /deleteditems\.timestamp/ );
299		256
300	my @colfilter;	257	my @colfilter;
301	$colfilter[0] = @$filters[0] if ( $column =~ /dewey/ );	258	$colfilter[0] = @$filters[0] if ( $column =~ /items\.itemcallnumber/ );
302	$colfilter[1] = @$filters[1] if ( $column =~ /dewey/ );	259	$colfilter[1] = @$filters[1] if ( $column =~ /items\.itemcallnumber/ );
303	$colfilter[0] = @$filters[2] if ( $column =~ /lccn/ );
304	$colfilter[1] = @$filters[3] if ( $column =~ /lccn/ );
305	$colfilter[0] = @$filters[4] if ( $column =~ /items\.itemcallnumber/ );
306	$colfilter[1] = @$filters[5] if ( $column =~ /items\.itemcallnumber/ );
307	if ( C4::Context->preference('item-level_itypes') ) {	260	if ( C4::Context->preference('item-level_itypes') ) {
308	$colfilter[0] = @$filters[6] if ( $column =~ /items\.itype/ );	261	$colfilter[0] = @$filters[2] if ( $column =~ /items\.itype/ );
309	} else {	262	} else {
310	$colfilter[0] = @$filters[6] if ( $column =~ /itemtype/ );	263	$colfilter[0] = @$filters[2] if ( $column =~ /itemtype/ );
311	}	264	}
312	$colfilter[0] = @$filters[7] if ( $column =~ /publishercode/ );	265	$colfilter[0] = @$filters[3] if ( $column =~ /publishercode/ );
313	$colfilter[0] = @$filters[8] if ( $column =~ /publicationyear/ );	266	$colfilter[0] = @$filters[4] if ( $column =~ /publicationyear/ );
314	$colfilter[1] = @$filters[9] if ( $column =~ /publicationyear/ );	267	$colfilter[1] = @$filters[5] if ( $column =~ /publicationyear/ );
315	$colfilter[0] = @$filters[10] if ( $column =~ /items\.homebranch/ );	268	$colfilter[0] = @$filters[6] if ( $column =~ /items\.homebranch/ );
316	$colfilter[0] = @$filters[11] if ( $column =~ /items\.location/ );	269	$colfilter[0] = @$filters[7] if ( $column =~ /items\.location/ );
317	$colfilter[0] = @$filters[12] if ( $column =~ /items\.ccode/ );	270	$colfilter[0] = @$filters[8] if ( $column =~ /items\.ccode/ );
318	$colfilter[0] = @$filters[13] if ( $column =~ /items\.notforloan/ );	271	$colfilter[0] = @$filters[9] if ( $column =~ /items\.notforloan/ );
319	$colfilter[0] = @$filters[14] if ( $column =~ /items\.materials/ );	272	$colfilter[0] = @$filters[10] if ( $column =~ /items\.materials/ );
320	$colfilter[0] = @$filters[17] if ( $column =~ /items.dateaccessioned/ );	273	$colfilter[0] = @$filters[13] if ( $column =~ /items.dateaccessioned/ );
321	$colfilter[1] = @$filters[18] if ( $column =~ /items\.dateaccessioned/ );	274	$colfilter[1] = @$filters[14] if ( $column =~ /items\.dateaccessioned/ );
322	$colfilter[0] = @$filters[19] if ( $column =~ /deleteditems\.timestamp/ );	275	$colfilter[0] = @$filters[15] if ( $column =~ /deleteditems\.timestamp/ );
323	$colfilter[1] = @$filters[20] if ( $column =~ /deleteditems\.timestamp/ );	276	$colfilter[1] = @$filters[16] if ( $column =~ /deleteditems\.timestamp/ );
324		277
325	# 1st, loop rows.	278	# 1st, loop rows.
326	my $origline = $line;	279	my $origline = $line;
327	$line =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");	280	$line =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
328	my $linefield;	281	my $linefield;
329	if ( ( $line =~ /dewey/ ) and ($deweydigits) ) {	282	if ( ( $line =~ /itemcallnumber/ ) and ($cotedigits) ) {
330	$linefield = "left($line,$deweydigits)";
331	} elsif ( ( $line =~ /lccn/ ) and ($lccndigits) ) {
332	$linefield = "left($line,$lccndigits)";
333	} elsif ( ( $line =~ /itemcallnumber/ ) and ($cotedigits) ) {
334	$linefield = "left($line,$cotedigits)";	283	$linefield = "left($line,$cotedigits)";
335	} elsif ( $line =~ /^deleteditems\.timestamp$/ ) {	284	} elsif ( $line =~ /^deleteditems\.timestamp$/ ) {
336	$linefield = "DATE($line)";	285	$linefield = "DATE($line)";
Lines 391-401 sub calculate { Link Here
391	my $origcolumn = $column;	340	my $origcolumn = $column;
392	$column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");	341	$column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");
393	my $colfield;	342	my $colfield;
394	if ( ( $column =~ /dewey/ ) and ($deweydigits) ) {	343	if ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {
395	$colfield = "left($column,$deweydigits)";
396	} elsif ( ( $column =~ /lccn/ ) and ($lccndigits) ) {
397	$colfield = "left($column,$lccndigits)";
398	} elsif ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {
399	$colfield = "left($column,$cotedigits)";	344	$colfield = "left($column,$cotedigits)";
400	} elsif ( $column =~ /^deleteditems\.timestamp$/ ) {	345	} elsif ( $column =~ /^deleteditems\.timestamp$/ ) {
401	$colfield = "DATE($column)";	346	$colfield = "DATE($column)";
Lines 474-556 sub calculate { Link Here
474		419
475	if ( @$filters[0] ) {	420	if ( @$filters[0] ) {
476	@$filters[0] =~ s/\*/%/g;	421	@$filters[0] =~ s/\*/%/g;
477	$strcalc .= " AND dewey >" . @$filters[0];	422	$strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[0] );
478	}	423	}
		424
479	if ( @$filters[1] ) {	425	if ( @$filters[1] ) {
480	@$filters[1] =~ s/\*/%/g;	426	@$filters[1] =~ s/\*/%/g;
481	$strcalc .= " AND dewey <" . @$filters[1];	427	$strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[1] );
482	}	428	}
		429
483	if ( @$filters[2] ) {	430	if ( @$filters[2] ) {
484	@$filters[2] =~ s/\*/%/g;	431	@$filters[2] =~ s/\*/%/g;
485	$strcalc .= " AND lccn >" . @$filters[2];	432	$strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[2] . "'";
486	}	433	}
		434
487	if ( @$filters[3] ) {	435	if ( @$filters[3] ) {
488	@$filters[3] =~ s/\*/%/g;	436	@$filters[3] =~ s/\*/%/g;
489	$strcalc .= " AND lccn <" . @$filters[3];	437	@$filters[3] .= "%" unless @$filters[3] =~ /%/;
		438	$strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[3] . "\"";
490	}	439	}
491	if ( @$filters[4] ) {	440	if ( @$filters[4] ) {
492	@$filters[4] =~ s/\*/%/g;	441	@$filters[4] =~ s/\*/%/g;
493	$strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[4] );	442	$strcalc .= " AND publicationyear >" . @$filters[4];
494	}	443	}
495
496	if ( @$filters[5] ) {	444	if ( @$filters[5] ) {
497	@$filters[5] =~ s/\*/%/g;	445	@$filters[5] =~ s/\*/%/g;
498	$strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[5] );	446	$strcalc .= " AND publicationyear <" . @$filters[5];
499	}	447	}
500
501	if ( @$filters[6] ) {	448	if ( @$filters[6] ) {
502	@$filters[6] =~ s/\*/%/g;	449	@$filters[6] =~ s/\*/%/g;
503	$strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[6] . "'";	450	$strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[6] . "'";
504	}	451	}
505
506	if ( @$filters[7] ) {	452	if ( @$filters[7] ) {
507	@$filters[7] =~ s/\*/%/g;	453	@$filters[7] =~ s/\*/%/g;
508	@$filters[7] .= "%" unless @$filters[7] =~ /%/;	454	$strcalc .= " AND $itemstable.location LIKE '" . @$filters[7] . "'";
509	$strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] . "\"";
510	}	455	}
511	if ( @$filters[8] ) {	456	if ( @$filters[8] ) {
512	@$filters[8] =~ s/\*/%/g;	457	@$filters[8] =~ s/\*/%/g;
513	$strcalc .= " AND publicationyear >" . @$filters[8];	458	$strcalc .= " AND $itemstable.ccode LIKE '" . @$filters[8] . "'";
514	}	459	}
515	if ( @$filters[9] ) {	460	if ( defined @$filters[9] and @$filters[9] ne '' ) {
516	@$filters[9] =~ s/\*/%/g;	461	@$filters[9] =~ s/\*/%/g;
517	$strcalc .= " AND publicationyear <" . @$filters[9];	462	$strcalc .= " AND $itemstable.notforloan LIKE '" . @$filters[13] . "'";
518	}	463	}
519	if ( @$filters[10] ) {	464	if ( defined @$filters[10] and @$filters[10] ne '' ) {
520	@$filters[10] =~ s/\*/%/g;	465	@$filters[10] =~ s/\*/%/g;
521	$strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[10] . "'";	466	$strcalc .= " AND $itemstable.materials LIKE '" . @$filters[10] . "'";
522	}
523	if ( @$filters[11] ) {
524	@$filters[11] =~ s/\*/%/g;
525	$strcalc .= " AND $itemstable.location LIKE '" . @$filters[11] . "'";
526	}
527	if ( @$filters[12] ) {
528	@$filters[12] =~ s/\*/%/g;
529	$strcalc .= " AND $itemstable.ccode LIKE '" . @$filters[12] . "'";
530	}	467	}
531	if ( defined @$filters[13] and @$filters[13] ne '' ) {	468	if ( @$filters[13] ) {
532	@$filters[13] =~ s/\*/%/g;	469	@$filters[13] =~ s/\*/%/g;
533	$strcalc .= " AND $itemstable.notforloan LIKE '" . @$filters[13] . "'";	470	$strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[13]' ";
534	}	471	}
535	if ( defined @$filters[14] and @$filters[14] ne '' ) {	472	if ( @$filters[14] ) {
536	@$filters[14] =~ s/\*/%/g;	473	@$filters[14] =~ s/\*/%/g;
537	$strcalc .= " AND $itemstable.materials LIKE '" . @$filters[14] . "'";	474	$strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[14]' ";
538	}
539	if ( @$filters[17] ) {
540	@$filters[17] =~ s/\*/%/g;
541	$strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[17]' ";
542	}
543	if ( @$filters[18] ) {
544	@$filters[18] =~ s/\*/%/g;
545	$strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[18]' ";
546	}	475	}
547	if ( $cellvalue eq 'deleteditems' and @$filters[19] ) {	476	if ( $cellvalue eq 'deleteditems' and @$filters[15] ) {
548	@$filters[19] =~ s/\*/%/g;	477	@$filters[15] =~ s/\*/%/g;
549	$strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[19]' ";	478	$strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[15]' ";
550	}	479	}
551	if ( $cellvalue eq 'deleteditems' and @$filters[20] ) {	480	if ( $cellvalue eq 'deleteditems' and @$filters[16] ) {
552	@$filters[20] =~ s/\*/%/g;	481	@$filters[16] =~ s/\*/%/g;
553	$strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[20]' ";	482	$strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[16]' ";
554	}	483	}
555	$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";	484	$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
556	$debug and warn "SQL: $strcalc";	485	$debug and warn "SQL: $strcalc";
557	-