Attachment #44652 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 = {
#       values   => \@select,
#   };

# 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;

    my $itemtypes = GetItemTypes( style => 'array' );


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

    $template->param(
        hasdewey     => $hasdewey,
        haslccn      => $haslccn,
        hascote      => $hascote,
        itemtypes    => $itemtypes,
        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 " .
        (C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate')
        . ">" . @$filters[4];
    }

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