Attachment #11100 for bug #8443

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




    my $dbh = C4::Context->dbh;
    my @sql_params;
    my @query = (
    q/SELECT suggestions.*,
        U1.branchcode   AS branchcodesuggestedby,
        B1.branchname   AS branchnamesuggestedby,
        U1.surname   AS surnamesuggestedby,

    LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
    LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
    LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
    WHERE 1=1/
    } , map {
        if ( my $s = $suggestion->{$_} ) {
        push @sql_params,'%'.$s.'%'; 
        " and suggestions.$_ like ? ";
        } else { () }
    } qw( title author isbn publishercode collectiontitle )
    );
    
    # filter on biblio informations
    foreach my $field (
        grep {
            my $fieldname = $_;
            any { $fieldname eq $_ } qw/title author isbn publishercode copyrightdate collectiontitle/
        } keys %$suggestion
      )
    {
        if ( $suggestion->{$field} ) {
            push @sql_params, $suggestion->{$field};
            push @query, " AND suggestions.$field = ?";
        }
    }

    # filter on user branch
    my $userenv = C4::Context->userenv;
    if (C4::Context->preference('IndependantBranches')) {
        if ($userenv) {
            if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){
                push @sql_params, $userenv->{branch};
                push @query, q/ AND (suggestions.branchcode = ? or suggestions.branchcode = '')/;
                }
            }
        }
    }

    # filter on nillable fields
    foreach my $field (
        grep {
            my $fieldname = $_;
            any { $fieldname eq $_ } qw/STATUS branchcode itemtype suggestedby managedby acceptedby bookfundid biblionumber/
        } keys %$suggestion
      )
    {
        if ( $suggestion->{$field} ) {
            push @sql_params, $suggestion->{$field};
            push @query, " AND suggestions.$field = ?";
        }
        else {
            push @query, " AND (suggestions.$field = '' OR suggestions.$field IS NULL)";
        }
    }

    # filter on date fields
    my $today = C4::Dates->today('iso');
    foreach my $field ( qw/suggesteddate manageddate accepteddate/ ) {
        my $from = $field."_from";
        my $to = $field."_to";
        if ($suggestion->{$from} || $suggestion->{$to}) {
            push @query, " AND suggestions.$field BETWEEN ? AND ?";
            push @sql_params, format_date_in_iso($suggestion->{$from}) || '0000-00-00';
            push @sql_params, format_date_in_iso($suggestion->{$to}) || $today;
        }
    }

    $debug && warn "@query";

    $sth->execute(@sql_params);
    my @results;
    while ( my $data=$sth->fetchrow_hashref ){
        # add status as field
        $data->{$data->{STATUS}} = 1;
        push(@results,$data);
    }
    return (\@results);




                    <li><label for="author"> Author:</label><input type="text" id="author" name="author" value="[% author %]" /></li>
                    <li><label for="isbn"> ISBN:</label><input type="text" id="isbn"  name="isbn" value="[% isbn %]" /></li>
                    <li><label for="publishercode"> Publisher:</label><input type="text" id="publishercode" name="publishercode" value="[% publishercode %]" /></li>
                    <li><label for="copyrightdate_filter"> Copyright date:</label><input type="text" id="copyrightdate_filter" name="copyrightdate" value="[% copyrightdate %]" /></li>
                    <li><label for="collectiontitle"> Collection title:</label><input type="text" id="collectiontitle" name="collectiontitle" value="[% collectiontitle %]" /></li><li><input type="submit" value="Go" /></li></ol>
                </fieldset>
				 <fieldset class="brief"><h4 class="collapse"><a href="#">Suggestion information</a></h4>

                    <li><label for="suggesteddate_from"> Suggested on:</label>[% suggesteddate %]
                        <table class="invis">
                        <tr>
                        <td><label for="suggesteddate_from">from:</label></td><td><input type="text" id="suggesteddate_from" name="suggesteddate_from" style="width:76px;" /></td>
                        </tr>
                        <tr>
                        <td><label for="suggesteddate_to">to:</label></td><td><input type="text" id="suggesteddate_to" name="suggesteddate_to" style="width:76px;" /></td>
                        </tr>
                        </table>
                    </li>

                    <li><label for="manageddate_from"> Management date:</label>[% manageddate %]
                        <table class="invis">
                        <tr>
                        <td><label for="manageddate_from">from:</label></td><td><input type="text" id="manageddate_from" name="manageddate_from" style="width:76px;" /></td>
                        </tr>
                        <tr>
                        <td><label for="manageddate_to">to:</label></td><td><input type="text" id="manageddate_to" name="manageddate_to" style="width:76px;" /></td>
                        </tr>
                        </table>
                    </li>

                    <li><label for="accepteddate_from"> Accepted on:</label>[% accepteddate %]
                        <table class="invis">
                        <tr>
                        <td><label for="accepteddate_from">from:</label></td><td><input type="text" id="accepteddate_from" name="accepteddate_from" style="width:76px;" /></td>
                        </tr>
                        <tr>
                        <td><label for="accepteddate_to">to:</label></td><td><input type="text" id="accepteddate_to" name="accepteddate_to" style="width:76px;" /></td>
                        </tr>
                        </table>
                    </li>




if ($op=~/else/) {
    $op='else';
    
    $displayby||="STATUS"; # by default, organize by status
    delete $$suggestion_ref{'branchcode'} if($displayby eq "branchcode");
    my $criteria_list=GetDistinctValues("suggestions.".$displayby);
    my %criteria_dv;
    foreach (@$criteria_list) {
        my $criteria_key;
        if ($_->{value}) {
            $criteria_key = $_->{value};
        } else {
            # agregate null and empty values under empty value
            $criteria_key = '';
        }
        my $criteria_cnt = $criteria_dv{$criteria_key} || 0;
        $criteria_dv{$criteria_key} = $criteria_cnt + $_->{cnt};
    }
    my @allsuggestions;
    my $reasonsloop = GetAuthorisedValues("SUGGEST");
    foreach my $criteriumvalue (keys %criteria_dv) {
        # By default, display suggestions from current working branch
        if(not defined $branchfilter) {
            $$suggestion_ref{'branchcode'} = C4::Context->userenv->{'branch'};
- 

Return to bug 8443

Lines 93-99 sub SearchSuggestion { Link Here

(-)a/C4/Suggestions.pm (-33 / +46 lines)
93	my $dbh = C4::Context->dbh;	93	my $dbh = C4::Context->dbh;
94	my @sql_params;	94	my @sql_params;
95	my @query = (	95	my @query = (
96	q{ SELECT suggestions.*,	96	q/SELECT suggestions.*,
97	U1.branchcode AS branchcodesuggestedby,	97	U1.branchcode AS branchcodesuggestedby,
98	B1.branchname AS branchnamesuggestedby,	98	B1.branchname AS branchnamesuggestedby,
99	U1.surname AS surnamesuggestedby,	99	U1.surname AS surnamesuggestedby,
Lines 115-163 sub SearchSuggestion { Link Here
115	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber	115	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
116	LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode	116	LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
117	LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode	117	LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
118	WHERE 1=1	118	WHERE 1=1/
119	} , map {
120	if ( my $s = $suggestion->{$_} ) {
121	push @sql_params,'%'.$s.'%';
122	" and suggestions.$_ like ? ";
123	} else { () }
124	} qw( title author isbn publishercode collectiontitle )
125	);	119	);
		120
		121	# filter on biblio informations
		122	foreach my $field (
		123	grep {
		124	my $fieldname = $_;
		125	any { $fieldname eq $_ } qw/title author isbn publishercode copyrightdate collectiontitle/
		126	} keys %$suggestion
		127	)
		128	{
		129	if ( $suggestion->{$field} ) {
		130	push @sql_params, $suggestion->{$field};
		131	push @query, " AND suggestions.$field = ?";
		132	}
		133	}
126		134
		135	# filter on user branch
127	my $userenv = C4::Context->userenv;	136	my $userenv = C4::Context->userenv;
128	if (C4::Context->preference('IndependantBranches')) {	137	if (C4::Context->preference('IndependantBranches')) {
129	if ($userenv) {	138	if ($userenv) {
130	if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){	139	if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){
131	push @sql_params,$$userenv{branch};	140	push @sql_params, $userenv->{branch};
132	push @query,q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')};	141	push @query, q/ AND (suggestions.branchcode = ? or suggestions.branchcode = '')/;
133	}
134	}	142	}
		143	}
135	}	144	}
136		145
137	foreach my $field (grep { my $fieldname=$_;	146	# filter on nillable fields
138	any {$fieldname eq $_ } qw<	147	foreach my $field (
139	STATUS branchcode itemtype suggestedby managedby acceptedby	148	grep {
140	bookfundid biblionumber	149	my $fieldname = $_;
141	>} keys %$suggestion	150	any { $fieldname eq $_ } qw/STATUS branchcode itemtype suggestedby managedby acceptedby bookfundid biblionumber/
142	) {	151	} keys %$suggestion
143	if ($$suggestion{$field}){	152	)
144	push @sql_params,$suggestion->{$field};	153	{
145	push @query, " and suggestions.$field=?";	154	if ( $suggestion->{$field} ) {
146	}	155	push @sql_params, $suggestion->{$field};
		156	push @query, " AND suggestions.$field = ?";
		157	}
147	else {	158	else {
148	push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";	159	push @query, " AND (suggestions.$field = '' OR suggestions.$field IS NULL)";
149	}	160	}
150	}	161	}
151		162
		163	# filter on date fields
152	my $today = C4::Dates->today('iso');	164	my $today = C4::Dates->today('iso');
153		165	foreach my $field ( qw/suggesteddate manageddate accepteddate/ ) {
154	foreach ( qw( suggesteddate manageddate accepteddate ) ) {	166	my $from = $field."_from";
155	my $from = $_ . "_from";	167	my $to = $field."_to";
156	my $to = $_ . "_to";	168	if ($suggestion->{$from} \|\| $suggestion->{$to}) {
157	if ($$suggestion{$from} \|\| $$suggestion{$to}) {	169	push @query, " AND suggestions.$field BETWEEN ? AND ?";
158	push @query, " AND suggestions.suggesteddate BETWEEN '"	170	push @sql_params, format_date_in_iso($suggestion->{$from}) \|\| '0000-00-00';
159	. (format_date_in_iso($$suggestion{$from}) \|\| 0000-00-00) . "' AND '" . (format_date_in_iso($$suggestion{$to}) \|\| $today) . "'";	171	push @sql_params, format_date_in_iso($suggestion->{$to}) \|\| $today;
160	}	172	}
161	}	173	}
162		174
163	$debug && warn "@query";	175	$debug && warn "@query";
Lines 165-171 sub SearchSuggestion { Link Here
165	$sth->execute(@sql_params);	177	$sth->execute(@sql_params);
166	my @results;	178	my @results;
167	while ( my $data=$sth->fetchrow_hashref ){	179	while ( my $data=$sth->fetchrow_hashref ){
168	$$data{$$data{STATUS}} = 1;	180	# add status as field
		181	$data->{$data->{STATUS}} = 1;
169	push(@results,$data);	182	push(@results,$data);
170	}	183	}
171	return (\@results);	184	return (\@results);

Lines 493-499 $(document).ready(function() { calcNewsuggTotal(); }); Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt (-7 / +7 lines)
493	<li><label for="author"> Author:</label><input type="text" id="author" name="author" value="[% author %]" /></li>	493	<li><label for="author"> Author:</label><input type="text" id="author" name="author" value="[% author %]" /></li>
494	<li><label for="isbn"> ISBN:</label><input type="text" id="isbn" name="isbn" value="[% isbn %]" /></li>	494	<li><label for="isbn"> ISBN:</label><input type="text" id="isbn" name="isbn" value="[% isbn %]" /></li>
495	<li><label for="publishercode"> Publisher:</label><input type="text" id="publishercode" name="publishercode" value="[% publishercode %]" /></li>	495	<li><label for="publishercode"> Publisher:</label><input type="text" id="publishercode" name="publishercode" value="[% publishercode %]" /></li>
496	<li><label for="publicationyear"> Publication year:</label><input type="text" id="publicationyear" name="publicationyear" value="[% publicationyear %]" /></li>	496	<li><label for="copyrightdate_filter"> Copyright date:</label><input type="text" id="copyrightdate_filter" name="copyrightdate" value="[% copyrightdate %]" /></li>
497	<li><label for="collectiontitle"> Collection title:</label><input type="text" id="collectiontitle" name="collectiontitle" value="[% collectiontitle %]" /></li><li><input type="submit" value="Go" /></li></ol>	497	<li><label for="collectiontitle"> Collection title:</label><input type="text" id="collectiontitle" name="collectiontitle" value="[% collectiontitle %]" /></li><li><input type="submit" value="Go" /></li></ol>
498	</fieldset>	498	</fieldset>
499	<fieldset class="brief"><h4 class="collapse"><a href="#">Suggestion information</a></h4>	499	<fieldset class="brief"><h4 class="collapse"><a href="#">Suggestion information</a></h4>
Lines 514-523 $(document).ready(function() { calcNewsuggTotal(); }); Link Here
514	<li><label for="suggesteddate_from"> Suggested on:</label>[% suggesteddate %]	514	<li><label for="suggesteddate_from"> Suggested on:</label>[% suggesteddate %]
515	<table class="invis">	515	<table class="invis">
516	<tr>	516	<tr>
517	<td><label for="suggesteddate_from">from:</label></td><td><input type="text" id="suggesteddate_from" name="suggesteddate_from" style="width:80px;" /></td>	517	<td><label for="suggesteddate_from">from:</label></td><td><input type="text" id="suggesteddate_from" name="suggesteddate_from" style="width:76px;" /></td>
518	</tr>	518	</tr>
519	<tr>	519	<tr>
520	<td><label for="suggesteddate_to">to:</label></td><td><input type="text" id="suggesteddate_to" name="suggesteddate_to" style="width:80px;" /></td>	520	<td><label for="suggesteddate_to">to:</label></td><td><input type="text" id="suggesteddate_to" name="suggesteddate_to" style="width:76px;" /></td>
521	</tr>	521	</tr>
522	</table>	522	</table>
523	</li>	523	</li>
Lines 527-536 $(document).ready(function() { calcNewsuggTotal(); }); Link Here
527	<li><label for="manageddate_from"> Management date:</label>[% manageddate %]	527	<li><label for="manageddate_from"> Management date:</label>[% manageddate %]
528	<table class="invis">	528	<table class="invis">
529	<tr>	529	<tr>
530	<td><label for="manageddate_from">from:</label></td><td><input type="text" id="manageddate_from" name="manageddate_from" style="width:80px;" /></td>	530	<td><label for="manageddate_from">from:</label></td><td><input type="text" id="manageddate_from" name="manageddate_from" style="width:76px;" /></td>
531	</tr>	531	</tr>
532	<tr>	532	<tr>
533	<td><label for="manageddate_to">to:</label></td><td><input type="text" id="manageddate_to" name="manageddate_to" style="width:80px;" /></td>	533	<td><label for="manageddate_to">to:</label></td><td><input type="text" id="manageddate_to" name="manageddate_to" style="width:76px;" /></td>
534	</tr>	534	</tr>
535	</table>	535	</table>
536	</li>	536	</li>
Lines 540-549 $(document).ready(function() { calcNewsuggTotal(); }); Link Here
540	<li><label for="accepteddate_from"> Accepted on:</label>[% accepteddate %]	540	<li><label for="accepteddate_from"> Accepted on:</label>[% accepteddate %]
541	<table class="invis">	541	<table class="invis">
542	<tr>	542	<tr>
543	<td><label for="accepteddate_from">from:</label></td><td><input type="text" id="accepteddate_from" name="accepteddate_from" style="width:80px;" /></td>	543	<td><label for="accepteddate_from">from:</label></td><td><input type="text" id="accepteddate_from" name="accepteddate_from" style="width:76px;" /></td>
544	</tr>	544	</tr>
545	<tr>	545	<tr>
546	<td><label for="accepteddate_to">to:</label></td><td><input type="text" id="accepteddate_to" name="accepteddate_to" style="width:80px;" /></td>	546	<td><label for="accepteddate_to">to:</label></td><td><input type="text" id="accepteddate_to" name="accepteddate_to" style="width:76px;" /></td>
547	</tr>	547	</tr>
548	</table>	548	</table>
549	</li>	549	</li>

Lines 188-199 elsif ( $op eq 'show' ) { Link Here

(-)a/suggestion/suggestion.pl (-3 / +14 lines)
188	if ($op=~/else/) {	188	if ($op=~/else/) {
189	$op='else';	189	$op='else';
190		190
191	$displayby\|\|="STATUS";	191	$displayby\|\|="STATUS"; # by default, organize by status
192	delete $$suggestion_ref{'branchcode'} if($displayby eq "branchcode");	192	delete $$suggestion_ref{'branchcode'} if($displayby eq "branchcode");
193	my $criteria_list=GetDistinctValues("suggestions.".$displayby);	193	my $criteria_list=GetDistinctValues("suggestions.".$displayby);
		194	my %criteria_dv;
		195	foreach (@$criteria_list) {
		196	my $criteria_key;
		197	if ($_->{value}) {
		198	$criteria_key = $_->{value};
		199	} else {
		200	# agregate null and empty values under empty value
		201	$criteria_key = '';
		202	}
		203	my $criteria_cnt = $criteria_dv{$criteria_key} \|\| 0;
		204	$criteria_dv{$criteria_key} = $criteria_cnt + $_->{cnt};
		205	}
194	my @allsuggestions;	206	my @allsuggestions;
195	my $reasonsloop = GetAuthorisedValues("SUGGEST");	207	my $reasonsloop = GetAuthorisedValues("SUGGEST");
196	foreach my $criteriumvalue ( map { $$_{'value'} } @$criteria_list ) {	208	foreach my $criteriumvalue (keys %criteria_dv) {
197	# By default, display suggestions from current working branch	209	# By default, display suggestions from current working branch
198	if(not defined $branchfilter) {	210	if(not defined $branchfilter) {
199	$$suggestion_ref{'branchcode'} = C4::Context->userenv->{'branch'};	211	$$suggestion_ref{'branchcode'} = C4::Context->userenv->{'branch'};
200	-