$(document).ready(function() {

    function changeRemovedDateTrStatus() {

        // http://jqueryui.com/demos/datepicker/#date-range

        var Cellvalue = $("input[name='Cellvalue']:checked").val();

        var dates = $( "#from, #to" ).datepicker({

        if(Cellvalue == "deleteditems") {

            changeMonth: true,

            $("#removeddatetr").show();

            numberOfMonths: 1,

            $("input[value='deleteditems.timestamp']").attr('disabled', false);

            onSelect: function( selectedDate ) {

            $("#deldateFrom").attr('disabled', false);

                var option = this.id == "from" ? "minDate" : "maxDate",

            $("#deldateTo").attr('disabled', false);

                    instance = $( this ).data( "datepicker" );

        } else {

                    date = $.datepicker.parseDate(

            $("#removeddatetr").hide();

                        instance.settings.dateFormat ||

            $("input[value='deleteditems.timestamp']").attr('disabled', true).attr('checked', false);

                        $.datepicker._defaults.dateFormat,

            $("#deldateFrom").attr('disabled', true).val('');

                        selectedDate, instance.settings );

            $("#deldateTo").attr('disabled', true).val('');

                dates.not( this ).datepicker( "option", option, date );

        }

            }

    }

        });

    $(document).ready(function() {

	my $results = calculate($line, $column, $deweydigits, $lccndigits, $cotedigits, \@filters);

    my $results = calculate( $line, $column, $cellvalue, $deweydigits, $lccndigits, $cotedigits, \@filters );

	if ($output eq "screen"){

    if ( $output eq "screen" ) {

		$template->param(mainloop => $results);

        $template->param( mainloop => $results );

		output_html_with_http_headers $input, $cookie, $template->output;

        output_html_with_http_headers $input, $cookie, $template->output;

		exit;

        exit;

	} else {

    } else {

		print $input->header(-type => 'application/vnd.sun.xml.calc',

        print $input->header(

                                     -encoding    => 'utf-8',

            -type       => 'application/vnd.sun.xml.calc',

							 -attachment=>"$basename.csv",

            -encoding   => 'utf-8',

							 -name=>"$basename.csv" );

            -attachment => "$basename.csv",

		my $cols  = @$results[0]->{loopcol};

            -name       => "$basename.csv"

		my $lines = @$results[0]->{looprow};

        );

		print @$results[0]->{line} ."/". @$results[0]->{column} .$sep;

        my $cols  = @$results[0]->{loopcol};

		foreach my $col ( @$cols ) {

        my $lines = @$results[0]->{looprow};

			print $col->{coltitle}.$sep;

        print @$results[0]->{line} . "/" . @$results[0]->{column} . $sep;

		}

        foreach my $col (@$cols) {

		print "Total\n";

            print $col->{coltitle} . $sep;

		foreach my $line ( @$lines ) {

        }

			my $x = $line->{loopcell};

        print "Total\n";

			print $line->{rowtitle}.$sep;

        foreach my $line (@$lines) {

			foreach my $cell (@$x) {

            my $x = $line->{loopcell};

				print $cell->{value}.$sep;

            print $line->{rowtitle} . $sep;

			}

            foreach my $cell (@$x) {

			print $line->{totalrow};

                print $cell->{value} . $sep;

 			print "\n";

            }

	 	}

            print $line->{totalrow};

		print "TOTAL";

            print "\n";

		$cols = @$results[0]->{loopfooter};

        }

		foreach my $col ( @$cols ) {

        print "TOTAL";

			print $sep.$col->{totalcol};

        $cols = @$results[0]->{loopfooter};

		}

        foreach my $col (@$cols) {

		print $sep.@$results[0]->{total};

            print $sep. $col->{totalcol};

		exit;

        }

	}

        print $sep. @$results[0]->{total};

        exit;

    }

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

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

	$req->execute;

    $req->execute;

	my $CGIitemtype = $req->fetchall_arrayref({});

    my $CGIitemtype = $req->fetchall_arrayref( {} );

	my $authvals = GetKohaAuthorisedValues("items.ccode");

    my $authvals = GetKohaAuthorisedValues("items.ccode");

	my @authvals;

    my @authvals;

	foreach (sort {$authvals->{$a} cmp $authvals->{$b} || $a cmp $b} keys %$authvals) {

    foreach ( sort { $authvals->{$a} cmp $authvals->{$b} || $a cmp $b } keys %$authvals ) {

		push @authvals, { code => $_, description => $authvals->{$_} };

        push @authvals, { code => $_, description => $authvals->{$_} };

	}

    }

	my $locations = GetKohaAuthorisedValues("items.location");

    my $locations = GetKohaAuthorisedValues("items.location");

	my @locations;

    my @locations;

	foreach (sort keys %$locations) {

    foreach ( sort keys %$locations ) {

		push @locations, { code => $_, description => "$_ - " . $locations->{$_} };

        push @locations, { code => $_, description => "$_ - " . $locations->{$_} };

	}

    }

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

    foreach my $kohafield (qw(items.notforloan items.materials items.statisticvalue)) {

        my $subfield_structure = GetMarcSubfieldStructureFromKohaField($kohafield);

	$template->param(hasdewey=>$hasdewey,

        if($subfield_structure) {

					haslccn   => $haslccn,

            my $avlist;

					hascote   => $hascote,

            my $avcategory = $subfield_structure->{authorised_value};

					CGIItemType => $CGIitemtype,

            if($avcategory) {

					CGIBranch    => GetBranchesLoop(C4::Context->userenv->{'branch'}),

                $avlist = GetAuthorisedValues($avcategory);

					locationloop => \@locations,

            }

					authvals     => \@authvals,

            my $kf = $kohafield;

					CGIextChoice => \@mime,

            $kf =~ s/^items\.//;

					CGIsepChoice => GetDelimiterChoices,

            $template->param(

					item_itype => $item_itype

                $kf => 1,

					);

                $kf."_label" => $subfield_structure->{liblibrarian},

                $kf."_avlist" => $avlist

            );

        }

    }

    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,

        authvals     => \@authvals,

        CGIextChoice => \@mime,

        CGIsepChoice => GetDelimiterChoices,

        item_itype   => $item_itype,

        DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),

    );

	my ($line, $column, $deweydigits, $lccndigits, $cotedigits, $filters) = @_;

    my ( $line, $column, $cellvalue, $deweydigits, $lccndigits, $cotedigits, $filters ) = @_;

	my @mainloop;

    my @mainloop;

	my @loopfooter;

    my @loopfooter;

	my @loopcol;

    my @loopcol;

	my @loopline;

    my @loopline;

	my @looprow;

    my @looprow;

	my %globalline;

    my %globalline;

	my $grantotal =0;

    my $grantotal     = 0;

    my $barcodelike   = @$filters[13];

    my $barcodelike   = @$filters[16];

    my $barcodefilter = @$filters[14];

    my $barcodefilter = @$filters[17];

    my $itemstable = ($cellvalue eq 'deleteditems') ? 'deleteditems' : 'items';

# extract parameters

	my $dbh = C4::Context->dbh;

# if barcodefilter is empty set as %

    my $dbh = C4::Context->dbh;

if($barcodefilter){

    # Check if barcodefilter is "like" or "not like"

    # if barcodefilter is empty set as %

    if(!$barcodelike){

    if ($barcodefilter) {

        $not = "not";

        # Check if barcodefilter is "like" or "not like"

        if ( !$barcodelike ) {

            $not = "not";

        }

        # Change * to %

        $barcodefilter =~ s/\*/%/g;

# Filters

    # Filters

# Checking filters

    # Checking filters

#

    #

	my @loopfilter;

    my @loopfilter;

	for (my $i=0;$i<=12;$i++) {

    for ( my $i = 0 ; $i <= @$filters ; $i++ ) {

		my %cell;

        my %cell;

		if ( @$filters[$i] ) {

        if ( defined @$filters[$i] and @$filters[$i] ne '' and $i != 16 ) {

			if ((($i==1) or ($i==3) or ($i==5) or ($i==9)) and (@$filters[$i-1])) {

            if ( ( ( $i == 1 ) or ( $i == 3 ) or ( $i == 5 ) or ( $i == 9 ) ) and ( @$filters[ $i - 1 ] ) ) {

				$cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;

                $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );

			}

            }

			$cell{filter} .= @$filters[$i];

            $cell{filter} .= @$filters[$i];

			$cell{crit} .=

            $cell{crit} .=

				($i== 0) ? "Dewey Classification From" :

                ( $i == 0 )  ? "Dewey Classification From"

				($i== 1) ? "Dewey Classification To"   :

              : ( $i == 1 )  ? "Dewey Classification To"

				($i== 2) ? "Lccn Classification From"  :

              : ( $i == 2 )  ? "Lccn Classification From"

				($i== 3) ? "Lccn Classification To"    :

              : ( $i == 3 )  ? "Lccn Classification To"

				($i== 4) ? "Item CallNumber From"  :

              : ( $i == 4 )  ? "Item CallNumber From"

				($i== 5) ? "Item CallNumber To"    :

              : ( $i == 5 )  ? "Item CallNumber To"

				($i== 6) ? "Item type"             :

              : ( $i == 6 )  ? "Item type"

				($i== 7) ? "Publisher"                 :

              : ( $i == 7 )  ? "Publisher"

				($i== 8) ? "Publication year From"     :

              : ( $i == 8 )  ? "Publication year From"

				($i== 9) ? "Publication year To"       :

              : ( $i == 9 )  ? "Publication year To"

				($i==10) ? "Library :"                  :

              : ( $i == 10 ) ? "Library"

				($i==11) ? "Shelving Location :"                :

              : ( $i == 11 ) ? "Shelving Location"

				($i==12) ? "Collection Code :"            : '';

              : ( $i == 12 ) ? "Collection Code"

			push @loopfilter, \%cell;

              : ( $i == 13 ) ? "Status"

		}

              : ( $i == 14 ) ? "Materials"

	}

              : ( $i == 15 ) ? "Statistic value"

              : ( $i == 17 and $filters->[16] == 0 ) ? "Barcode (not like)"

#	warn map {"filtres $_\n"} @filters[0..3];

              : ( $i == 17 and $filters->[16] == 1 ) ? "Barcode (like)"

              : ( $i == 18 ) ? "Acquired date from"

	my @linefilter;

              : ( $i == 19 ) ? "Acquired date to"

 	$linefilter[0] = @$filters[0] if ($line =~ /dewey/ )  ;

              : ( $i == 20 ) ? "Removed date from"

 	$linefilter[1] = @$filters[1] if ($line =~ /dewey/ )  ;

              : ( $i == 21 ) ? "Removed date to"

 	$linefilter[0] = @$filters[2] if ($line =~ /lccn/ )  ;

              :                '';

 	$linefilter[1] = @$filters[3] if ($line =~ /lccn/ )  ;

 	$linefilter[0] = @$filters[4] if ($line =~ /items\.itemcallnumber/ )  ;

            push @loopfilter, \%cell;

 	$linefilter[1] = @$filters[5] if ($line =~ /items\.itemcallnumber/ )  ;

        }

	if (C4::Context->preference('item-level_itypes')) {

    }

 		$linefilter[0] = @$filters[6] if ($line =~ /items\.itype/ )  ;

	} else {

    @$filters[18] = C4::Dates->new(@$filters[18])->output('iso') if @$filters[18];

 		$linefilter[0] = @$filters[6] if ($line =~ /itemtype/ )  ;

    @$filters[19] = C4::Dates->new(@$filters[19])->output('iso') if @$filters[19];

	}

    @$filters[20] = C4::Dates->new(@$filters[20])->output('iso') if @$filters[20];

 	$linefilter[0] = @$filters[7] if ($line =~ /publishercode/ ) ;

    @$filters[21] = C4::Dates->new(@$filters[21])->output('iso') if @$filters[21];

 	$linefilter[0] = @$filters[8] if ($line =~ /publicationyear/ ) ;

 	$linefilter[1] = @$filters[9] if ($line =~ /publicationyear/ ) ;

    my @linefilter;

 	$linefilter[0] = @$filters[10] if ($line =~ /items\.homebranch/ ) ;

    $linefilter[0] = @$filters[0] if ( $line =~ /dewey/ );

 	$linefilter[0] = @$filters[11] if ($line =~ /items\.location/ ) ;

    $linefilter[1] = @$filters[1] if ( $line =~ /dewey/ );

 	$linefilter[0] = @$filters[12] if ($line =~ /items\.ccode/ ) ;

    $linefilter[0] = @$filters[2] if ( $line =~ /lccn/ );

    $linefilter[1] = @$filters[3] if ( $line =~ /lccn/ );

 	my @colfilter ;

    $linefilter[0] = @$filters[4] if ( $line =~ /items\.itemcallnumber/ );

 	$colfilter[0] = @$filters[0] if ($column =~ /dewey/ )  ;

    $linefilter[1] = @$filters[5] if ( $line =~ /items\.itemcallnumber/ );

 	$colfilter[1] = @$filters[1] if ($column =~ /dewey/ )  ;

    if ( C4::Context->preference('item-level_itypes') ) {

 	$colfilter[0] = @$filters[2] if ($column =~ /lccn/ )  ;

        $linefilter[0] = @$filters[6] if ( $line =~ /items\.itype/ );

 	$colfilter[1] = @$filters[3] if ($column =~ /lccn/ )  ;

    } else {

 	$colfilter[0] = @$filters[4] if ($column =~ /items\.itemcallnumber/ )  ;

        $linefilter[0] = @$filters[6] if ( $line =~ /itemtype/ );

 	$colfilter[1] = @$filters[5] if ($column =~ /items\.itemcallnumber/ )  ;

    }

	if (C4::Context->preference('item-level_itypes')) {

    $linefilter[0] = @$filters[7] if ( $line =~ /publishercode/ );

 		$colfilter[0] = @$filters[6] if ($column =~ /items\.itype/ )  ;

    $linefilter[0] = @$filters[8] if ( $line =~ /publicationyear/ );

	} else {

    $linefilter[1] = @$filters[9] if ( $line =~ /publicationyear/ );

 		$colfilter[0] = @$filters[6] if ($column =~ /itemtype/ )  ;

	}

    $linefilter[0] = @$filters[10] if ( $line =~ /items\.homebranch/ );

 	$colfilter[0] = @$filters[7] if ($column =~ /publishercode/ ) ;

    $linefilter[0] = @$filters[11] if ( $line =~ /items\.location/ );

 	$colfilter[0] = @$filters[8] if ($column =~ /publicationyear/ ) ;

    $linefilter[0] = @$filters[12] if ( $line =~ /items\.ccode/ );

 	$colfilter[1] = @$filters[9] if ($column =~ /publicationyear/ ) ;

    $linefilter[0] = @$filters[13] if ( $line =~ /items\.notforloan/ );

 	$colfilter[0] = @$filters[10] if ($column =~ /items\.homebranch/ ) ;

    $linefilter[0] = @$filters[14] if ( $line =~ /items\.materials/ );

 	$colfilter[0] = @$filters[11] if ($column =~ /items\.location/ ) ;

    $linefilter[0] = @$filters[15] if ( $line =~ /items\.statisticvalue/ );

 	$colfilter[0] = @$filters[12] if ($column =~ /items\.ccode/ ) ;

    $linefilter[0] = @$filters[18] if ( $line =~ /items\.dateaccessioned/ );

    $linefilter[1] = @$filters[19] if ( $line =~ /items\.dateaccessioned/ );

# 1st, loop rows.

    $linefilter[0] = @$filters[20] if ( $line =~ /deleteditems\.timestamp/ );

	my $linefield;

    $linefilter[1] = @$filters[21] if ( $line =~ /deleteditems\.timestamp/ );

	if (($line =~/dewey/)  and ($deweydigits)) {

		$linefield .="left($line,$deweydigits)";

    my @colfilter;

	} elsif (($line=~/lccn/) and ($lccndigits)) {

    $colfilter[0] = @$filters[0] if ( $column =~ /dewey/ );

		$linefield .="left($line,$lccndigits)";

    $colfilter[1] = @$filters[1] if ( $column =~ /dewey/ );

	} elsif (($line=~/items.itemcallnumber/) and ($cotedigits)) {

    $colfilter[0] = @$filters[2] if ( $column =~ /lccn/ );

		$linefield .="left($line,$cotedigits)";

    $colfilter[1] = @$filters[3] if ( $column =~ /lccn/ );

	}else {

    $colfilter[0] = @$filters[4] if ( $column =~ /items\.itemcallnumber/ );

		$linefield .= $line;

    $colfilter[1] = @$filters[5] if ( $column =~ /items\.itemcallnumber/ );

	}

    if ( C4::Context->preference('item-level_itypes') ) {

        $colfilter[0] = @$filters[6] if ( $column =~ /items\.itype/ );

	my $strsth = "SELECT DISTINCTROW $linefield FROM biblioitems 

    } else {

                    INNER JOIN items USING (biblioitemnumber)

        $colfilter[0] = @$filters[6] if ( $column =~ /itemtype/ );

                    INNER JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)

    }

	              WHERE $line IS NOT NULL ";

    $colfilter[0] = @$filters[7]  if ( $column =~ /publishercode/ );

    $colfilter[0] = @$filters[8]  if ( $column =~ /publicationyear/ );

    $colfilter[1] = @$filters[9]  if ( $column =~ /publicationyear/ );

    $colfilter[0] = @$filters[10] if ( $column =~ /items\.homebranch/ );

    $colfilter[0] = @$filters[11] if ( $column =~ /items\.location/ );

    $colfilter[0] = @$filters[12] if ( $column =~ /items\.ccode/ );

    $colfilter[0] = @$filters[13] if ( $column =~ /items\.notforloan/ );

    $colfilter[0] = @$filters[14] if ( $column =~ /items\.materials/ );

    $colfilter[0] = @$filters[15] if ( $column =~ /items\.statisticvalue/ );

    $colfilter[0] = @$filters[18] if ( $column =~ /items.dateaccessioned/ );

    $colfilter[1] = @$filters[19] if ( $column =~ /items\.dateaccessioned/ );

    $colfilter[0] = @$filters[20] if ( $column =~ /deleteditems\.timestamp/ );

    $colfilter[1] = @$filters[21] if ( $column =~ /deleteditems\.timestamp/ );

    # 1st, loop rows.

    my $origline = $line;

    $line =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");

    my $linefield;

    if ( ( $line =~ /dewey/ ) and ($deweydigits) ) {

        $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)";

    } else {

        $linefield = $line;

    }

    my $strsth = "SELECT DISTINCTROW $linefield FROM $itemstable

                    LEFT JOIN biblioitems USING (biblioitemnumber)

                    LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)

                  WHERE 1 ";

	if ( @linefilter ) {

    if (@linefilter) {

		if ($linefilter[1]){

        if ( $linefilter[1] ) {

			$strsth .= " AND $line >= ? " ;

            $strsth .= " AND $line >= ? ";

			$strsth .= " AND $line <= ? " ;

            $strsth .= " AND $line <= ? ";

		} elsif ($linefilter[0]) {

        } elsif ( defined $linefilter[0] and $linefilter[0] ne '' ) {

			$linefilter[0] =~ s/\*/%/g;

            $linefilter[0] =~ s/\*/%/g;

			$strsth .= " AND $line LIKE ? " ;

            $strsth .= " AND $line LIKE ? ";

		}

        }

	}

    }

	$strsth .=" ORDER BY $linefield";

    $strsth .= " ORDER BY $linefield";

	$debug and print STDERR "catalogue_stats SQL: $strsth\n";

    $debug and print STDERR "catalogue_stats SQL: $strsth\n";

	my $sth = $dbh->prepare( $strsth );

    my $sth = $dbh->prepare($strsth);

	if (( @linefilter ) and ($linefilter[1])){

    if ( $barcodefilter and (@linefilter) and ( $linefilter[1] ) ) {

		$sth->execute($barcodefilter,$linefilter[0],$linefilter[1]);

        $sth->execute( $barcodefilter, $linefilter[0], $linefilter[1] );

	} elsif ($barcodefilter,$linefilter[0]) {

    } elsif ( (@linefilter) and ( $linefilter[1] ) ) {

		$sth->execute($barcodefilter,$linefilter[0]);

        $sth->execute( $linefilter[0], $linefilter[1] );

	} elsif ($barcodefilter) {

    } elsif ( $barcodefilter and $linefilter[0] ) {

		$sth->execute($barcodefilter);

        $sth->execute( $barcodefilter, $linefilter[0] );

	}else{

    } elsif ( $linefilter[0] ) {

	    $sth->execute();

        $sth->execute($linefilter[0]);

	}

    } elsif ($barcodefilter) {

 	while ( my ($celvalue) = $sth->fetchrow) {

        $sth->execute($barcodefilter);

 		my %cell;

    } else {

		if ($celvalue) {

        $sth->execute();

			$cell{rowtitle} = $celvalue;

    }

#		} else {

    my $rowauthvals = GetKohaAuthorisedValues($origline);

#			$cell{rowtitle} = "";

    while ( my ($celvalue) = $sth->fetchrow ) {

		}

        my %cell;

 		$cell{totalrow} = 0;

        if (defined $celvalue and $celvalue ne '') {

		push @loopline, \%cell;

            if($rowauthvals and $rowauthvals->{$celvalue}) {

 	}

                $cell{rowtitle} = $rowauthvals->{$celvalue};

            } else {

# 2nd, loop cols.

                $cell{rowtitle} = $celvalue;

	my $colfield;

            }

	if (($column =~/dewey/)  and ($deweydigits)) {

            $cell{value} = $celvalue;

		$colfield = "left($column,$deweydigits)";

        }

	}elsif (($column=~/lccn/) and ($lccndigits)) {

        else {

		$colfield = "left($column,$lccndigits)";

            $cell{rowtitle} = "NULL";

	}elsif (($column=~/itemcallnumber/) and ($cotedigits)) {

            $cell{value} = "zzEMPTY";

		$colfield = "left($column,$cotedigits)";

        }

	}else {

        $cell{totalrow} = 0;

		$colfield = $column;

        push @loopline, \%cell;

	}

    }

	my $strsth2 = "

    # 2nd, loop cols.

	SELECT distinctrow $colfield

    my $origcolumn = $column;

	FROM   biblioitems

    $column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems");

	INNER JOIN items

    my $colfield;

		USING (biblioitemnumber)

    if ( ( $column =~ /dewey/ ) and ($deweydigits) ) {

    INNER JOIN biblio

        $colfield = "left($column,$deweydigits)";

        ON (biblioitems.biblionumber = biblio.biblionumber)

    } elsif ( ( $column =~ /lccn/ ) and ($lccndigits) ) {

	WHERE $column IS NOT NULL ";

        $colfield = "left($column,$lccndigits)";

	$strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter;

    } elsif ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) {

        $colfield = "left($column,$cotedigits)";

	if (( @colfilter ) and ($colfilter[1])) {

    } elsif ( $column =~ /^deleteditems\.timestamp$/ ) {

		$strsth2 .= " AND $column> ? AND $column< ?";

        $colfield = "DATE($column)";

	}elsif ($colfilter[0]){

    } else {

		$colfilter[0] =~ s/\*/%/g;

        $colfield = $column;

		$strsth2 .= " AND $column LIKE ? ";

    }

	} 

	$strsth2 .= " ORDER BY $colfield";

    my $strsth2 = "

	$debug and print STDERR "SQL: $strsth2";

        SELECT distinctrow $colfield

	my $sth2 = $dbh->prepare( $strsth2 );

        FROM   $itemstable

	if ((@colfilter) and ($colfilter[1])) {

        LEFT JOIN biblioitems

		$sth2->execute($barcodefilter,$colfilter[0],$colfilter[1]);

            USING (biblioitemnumber)

	} elsif ($colfilter[0]){

        LEFT JOIN biblio

		$sth2->execute($barcodefilter,$colfilter[0]);

            ON (biblioitems.biblionumber = biblio.biblionumber)

	} elsif ($barcodefilter){

        WHERE 1 ";

	    $sth2->execute($barcodefilter);

    $strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter;

	} else {

		$sth2->execute();

    if ( (@colfilter) and ( $colfilter[1] ) ) {

	}

        $strsth2 .= " AND $column >= ? AND $column <= ?";

 	while (my ($celvalue) = $sth2->fetchrow) {

    } elsif ( defined $colfilter[0] and $colfilter[0] ne '' ) {

 		my %cell;

        $colfilter[0] =~ s/\*/%/g;

		my %ft;

        $strsth2 .= " AND $column LIKE ? ";

		if ($celvalue) {

    }

			$cell{coltitle} = $celvalue;

    $strsth2 .= " ORDER BY $colfield";

#		} else {

    $debug and print STDERR "SQL: $strsth2";

#			$cell{coltitle} = "";

    my $sth2 = $dbh->prepare($strsth2);

		}

    if ( $barcodefilter and (@colfilter) and ( $colfilter[1] ) ) {

 		$ft{totalcol} = 0;

        $sth2->execute( $barcodefilter, $colfilter[0], $colfilter[1] );

		push @loopcol, \%cell;

    } elsif ( (@colfilter) and ( $colfilter[1] ) ) {

 	}

        $sth2->execute( $colfilter[0], $colfilter[1] );

    } elsif ( $barcodefilter && $colfilter[0] ) {

	my $i=0;

        $sth2->execute( $barcodefilter , $colfilter[0] );

	my @totalcol;

    } elsif ( $colfilter[0]) {

	my $hilighted=-1;

        $sth2->execute( $colfilter[0] );

    } elsif ($barcodefilter) {

	#Initialization of cell values.....

        $sth2->execute($barcodefilter);

	my %table;

    } else {

#	warn "init table";

        $sth2->execute();

	foreach my $row ( @loopline ) {

    }

		foreach my $col ( @loopcol ) {

    my $colauthvals = GetKohaAuthorisedValues($origcolumn);

#			warn " init table : $row->{rowtitle} / $col->{coltitle} ";

    while ( my ($celvalue) = $sth2->fetchrow ) {

			$table{$row->{rowtitle}}->{$col->{coltitle}}=0;

        my %cell;

		}

        if (defined $celvalue and $celvalue ne '') {

		$table{$row->{rowtitle}}->{totalrow}=0;

            if($colauthvals and $colauthvals->{$celvalue}) {

	}

                $cell{coltitle} = $colauthvals->{$celvalue};

            } else {

# preparing calculation

                $cell{coltitle} = $celvalue;

            }

            $cell{value} = $celvalue;

        }

        else {

            $cell{coltitle} = "NULL";

            $cell{value} = "zzEMPTY";

        }

        $cell{totalcol} = 0;

        push @loopcol, \%cell;

    }

    my $i = 0;

    my @totalcol;

    my $hilighted = -1;

    #Initialization of cell values.....

    my %table;

    foreach my $row (@loopline) {

        foreach my $col (@loopcol) {

            $table{ $row->{value} }->{ $col->{value} } = 0;

        }

        $table{ $row->{value} }->{totalrow} = 0;

    }

    # preparing calculation

    my $select_cellvalue = " COUNT(*) ";

    $select_cellvalue = " COUNT(DISTINCT biblioitems.biblionumber) " if($cellvalue eq 'biblios');

        SELECT $linefield, $colfield, count(*)

        SELECT $linefield, $colfield, $select_cellvalue

        FROM biblioitems

        FROM $itemstable

        INNER JOIN items ON (items.biblioitemnumber = biblioitems.biblioitemnumber)

        LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber)

        INNER JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)

        LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)

	$strcalc .= "AND barcode $not like ? " if ($barcodefilter); 

    $strcalc .= "AND barcode $not like ? " if ($barcodefilter);

	if (@$filters[0]){

    if ( @$filters[0] ) {

		@$filters[0]=~ s/\*/%/g;

        @$filters[0] =~ s/\*/%/g;

		$strcalc .= " AND dewey >" . @$filters[0];

        $strcalc .= " AND dewey >" . @$filters[0];

	}

    }

	if (@$filters[1]){

    if ( @$filters[1] ) {

		@$filters[1]=~ s/\*/%/g ;

        @$filters[1] =~ s/\*/%/g;

		$strcalc .= " AND dewey <" . @$filters[1];

        $strcalc .= " AND dewey <" . @$filters[1];

	}

    }

	if (@$filters[2]){

    if ( @$filters[2] ) {

		@$filters[2]=~ s/\*/%/g ;

        @$filters[2] =~ s/\*/%/g;

		$strcalc .= " AND lccn >" . @$filters[2];

        $strcalc .= " AND lccn >" . @$filters[2];

	}

    }

	if (@$filters[3]){

    if ( @$filters[3] ) {

		@$filters[3]=~ s/\*/%/g;

        @$filters[3] =~ s/\*/%/g;

		$strcalc .= " AND lccn <" . @$filters[3];

        $strcalc .= " AND lccn <" . @$filters[3];

	}

    }

	if (@$filters[4]){

    if ( @$filters[4] ) {

		@$filters[4]=~ s/\*/%/g ;

        @$filters[4] =~ s/\*/%/g;

		$strcalc .= " AND items.itemcallnumber >=" . $dbh->quote(@$filters[4]);

        $strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[4] );

	}

    }

	if (@$filters[5]){

    if ( @$filters[5] ) {

		@$filters[5]=~ s/\*/%/g;

        @$filters[5] =~ s/\*/%/g;

		$strcalc .= " AND items.itemcallnumber <=" . $dbh->quote(@$filters[5]);

        $strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[5] );

	}

    }

	if (@$filters[6]){

    if ( @$filters[6] ) {

		@$filters[6]=~ s/\*/%/g;

        @$filters[6] =~ s/\*/%/g;

		$strcalc .= " AND " . 

        $strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[6] . "'";

			(C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype')

    }

			. " LIKE '" . @$filters[6] ."'";

	}

    if ( @$filters[7] ) {

        @$filters[7] =~ s/\*/%/g;

	if (@$filters[7]){

        @$filters[7] .= "%" unless @$filters[7] =~ /%/;

		@$filters[7]=~ s/\*/%/g;

        $strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] . "\"";

		@$filters[7].="%" unless @$filters[7]=~/%/;

    }

		$strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[7] ."\"";

    if ( @$filters[8] ) {

	}

        @$filters[8] =~ s/\*/%/g;

	if (@$filters[8]){

		@$filters[8]=~ s/\*/%/g;

	}

    }

	if (@$filters[9]){

    if ( @$filters[9] ) {

		@$filters[9]=~ s/\*/%/g;

        @$filters[9] =~ s/\*/%/g;

	}

    }

	if (@$filters[10]){

    if ( @$filters[10] ) {

		@$filters[10]=~ s/\*/%/g;

        @$filters[10] =~ s/\*/%/g;

		$strcalc .= " AND items.homebranch LIKE '" . @$filters[10] ."'";

        $strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[10] . "'";

	}

    }

	if (@$filters[11]){

    if ( @$filters[11] ) {

		@$filters[11]=~ s/\*/%/g;

        @$filters[11] =~ s/\*/%/g;

		$strcalc .= " AND items.location LIKE '" . @$filters[11] ."'";

        $strcalc .= " AND $itemstable.location LIKE '" . @$filters[11] . "'";

	}

    }

	if (@$filters[12]){

    if ( @$filters[12] ) {

		@$filters[12]=~ s/\*/%/g;

        @$filters[12] =~ s/\*/%/g;

		$strcalc .= " AND items.ccode  LIKE '" . @$filters[12] ."'";

        $strcalc .= " AND $itemstable.ccode  LIKE '" . @$filters[12] . "'";

	}

    }

    if ( defined @$filters[13] and @$filters[13] ne '' ) {

	$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";

        @$filters[13] =~ s/\*/%/g;

	$debug and warn "SQL: $strcalc";

        $strcalc .= " AND $itemstable.notforloan  LIKE '" . @$filters[13] . "'";

	my $dbcalc = $dbh->prepare($strcalc);

    }

	if($barcodefilter){

    if ( defined @$filters[14] and @$filters[14] ne '' ) {

	   $dbcalc->execute($barcodefilter);

        @$filters[14] =~ s/\*/%/g;

	}else{

        $strcalc .= " AND $itemstable.materials  LIKE '" . @$filters[14] . "'";

	   $dbcalc->execute();

    }

	}

    if ( defined @$filters[15] and @$filters[15] ne '' ) {

#	warn "filling table";

        @$filters[15] =~ s/\*/%/g;

	my $emptycol; 

        $strcalc .= " AND $itemstable.statisticvalue  LIKE '" . @$filters[15] . "'";

	while (my ($row, $col, $value) = $dbcalc->fetchrow) {

    }

#		warn "filling table $row / $col / $value ";

    if ( @$filters[18] ) {

		$emptycol = 1    if (!defined($col));

        @$filters[18] =~ s/\*/%/g;

		$col = "zzEMPTY" if (!defined($col));

        $strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[18]' ";

		$row = "zzEMPTY" if (!defined($row));

    }

    if ( @$filters[19] ) {

		$table{$row}->{$col}+=$value;

        @$filters[19] =~ s/\*/%/g;

		$table{$row}->{totalrow}+=$value;

        $strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[19]' ";

		$grantotal += $value;

    }

	}

    if ( $cellvalue eq 'deleteditems' and @$filters[20] ) {

        @$filters[20] =~ s/\*/%/g;

# 	my %cell = {rowtitle => 'zzROWEMPTY'};

        $strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[20]' ";

# 	push @loopline,\%cell;

    }

# 	undef %cell;

    if ( $cellvalue eq 'deleteditems' and @$filters[21] ) {

# 	my %cell;

        @$filters[21] =~ s/\*/%/g;

# 	%cell = {coltitle => "zzEMPTY"};

        $strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[21]' ";

 	push @loopcol,{coltitle => "NULL"} if ($emptycol);

    }

    $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";

	foreach my $row ( sort keys %table ) {

    $debug and warn "SQL: $strcalc";

		my @loopcell;

    my $dbcalc = $dbh->prepare($strcalc);

		#@loopcol ensures the order for columns is common with column titles

    if ($barcodefilter) {

		# and the number matches the number of columns

        $dbcalc->execute($barcodefilter);

		foreach my $col ( @loopcol ) {

    } else {

			my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};

        $dbcalc->execute();

			push @loopcell, {value => $value  } ;

    }

		}

		push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,

    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {

						'loopcell' => \@loopcell,

						'hilighted' => ($hilighted *= -1 > 0),

        $col      = "zzEMPTY" if ( !defined($col) );

						'totalrow' => $table{$row}->{totalrow}

        $row      = "zzEMPTY" if ( !defined($row) );

					};

	}

        $table{$row}->{$col}     += $value;

        $table{$row}->{totalrow} += $value;

#	warn "footer processing";

        $grantotal               += $value;

	foreach my $col ( @loopcol ) {

    }

		my $total=0;

		foreach my $row ( @looprow ) {

    foreach my $row ( @loopline ) {

			$total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};

        my @loopcell;

#			warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};

		}

        #@loopcol ensures the order for columns is common with column titles

#		warn "summ for column ".$col->{coltitle}."  = ".$total;

        # and the number matches the number of columns

		push @loopfooter, {'totalcol' => $total};

        foreach my $col (@loopcol) {

	}

            my $value = $table{$row->{value}}->{ $col->{value} };

            push @loopcell, { value => $value };

        }

	# the header of the table

        push @looprow,

	$globalline{loopfilter}=\@loopfilter;

          { 'rowtitle' => $row->{rowtitle},

	# the core of the table

            'value'    => $row->{value},

	$globalline{looprow} = \@looprow;

            'loopcell' => \@loopcell,

 	$globalline{loopcol} = \@loopcol;

            'hilighted' => ( $hilighted *= -1 > 0 ),

# 	# the foot (totals by borrower type)

            'totalrow' => $table{$row->{value}}->{totalrow}

 	$globalline{loopfooter} = \@loopfooter;

          };

 	$globalline{total}= $grantotal;

    }

	$globalline{line} = $line;

	$globalline{column} = $column;

    foreach my $col (@loopcol) {

	push @mainloop,\%globalline;

        my $total = 0;

	return \@mainloop;

        foreach my $row (@looprow) {

}

            $total += $table{ $row->{value} }->{ $col->{value} };

        }

1;

        push @loopfooter, { 'totalcol' => $total };

    }

    # the header of the table

    $globalline{loopfilter} = \@loopfilter;

    # the core of the table

    $globalline{looprow} = \@looprow;

    $globalline{loopcol} = \@loopcol;

    # the foot (totals by borrower type)

    $globalline{loopfooter} = \@loopfooter;

    $globalline{total}      = $grantotal;

    $globalline{line}       = $line;

    $globalline{column}     = $column;

    push @mainloop, \%globalline;

    return \@mainloop;

}

- 

#!/usr/bin/perl

use Modern::Perl;

use Test::More tests => 24;

use Data::Dumper;

BEGIN {

    use_ok('C4::Biblio');

}

my @columns = qw(

    tagfield tagsubfield liblibrarian libopac repeatable mandatory kohafield tab

    authorised_value authtypecode value_builder isurl hidden frameworkcode

    seealso link defaultvalue maxlength

);

# biblio.biblionumber must be mapped so this should return something

my $marc_subfield_structure = GetMarcSubfieldStructureFromKohaField('biblio.biblionumber', '');

ok(defined $marc_subfield_structure, "There is a result");

is(ref $marc_subfield_structure, "HASH", "Result is a hashref");

foreach my $col (@columns) {

    ok(exists $marc_subfield_structure->{$col}, "Hashref contains key '$col'");

}

is($marc_subfield_structure->{kohafield}, 'biblio.biblionumber', "Result is the good result");

like($marc_subfield_structure->{tagfield}, qr/^\d{3}$/, "tagfield is a valid tagfield");

# foo.bar does not exist so this should return undef

$marc_subfield_structure = GetMarcSubfieldStructureFromKohaField('foo.bar', '');

is($marc_subfield_structure, undef, "invalid kohafield returns undef");