|
Lines 72-88
This will return a list of all the available report areas
Link Here
|
| 72 |
|
72 |
|
| 73 |
=cut |
73 |
=cut |
| 74 |
|
74 |
|
| 75 |
my @REPORT_AREA = ( |
75 |
sub get_area_name_sql_snippet { |
| 76 |
[CIRC => "Circulation"], |
76 |
my @REPORT_AREA = ( |
| 77 |
[CAT => "Catalogue"], |
77 |
[CIRC => "Circulation"], |
| 78 |
[PAT => "Patrons"], |
78 |
[CAT => "Catalogue"], |
| 79 |
[ACQ => "Acquisition"], |
79 |
[PAT => "Patrons"], |
| 80 |
[ACC => "Accounts"], |
80 |
[ACQ => "Acquisition"], |
| 81 |
); |
81 |
[ACC => "Accounts"], |
| 82 |
my $AREA_NAME_SQL_SNIPPET |
82 |
); |
| 83 |
= "CASE report_area " . |
83 |
|
|
|
84 |
return "CASE report_area " . |
| 84 |
join (" ", map "WHEN '$_->[0]' THEN '$_->[1]'", @REPORT_AREA) . |
85 |
join (" ", map "WHEN '$_->[0]' THEN '$_->[1]'", @REPORT_AREA) . |
| 85 |
" END AS areaname"; |
86 |
" END AS areaname"; |
|
|
87 |
} |
| 86 |
|
88 |
|
| 87 |
sub get_report_areas { |
89 |
sub get_report_areas { |
| 88 |
|
90 |
|
|
Lines 91-138
sub get_report_areas {
Link Here
|
| 91 |
return $report_areas; |
93 |
return $report_areas; |
| 92 |
} |
94 |
} |
| 93 |
|
95 |
|
| 94 |
my %table_areas = ( |
96 |
sub get_table_areas { |
|
|
97 |
return ( |
| 95 |
CIRC => [ 'borrowers', 'statistics', 'items', 'biblioitems' ], |
98 |
CIRC => [ 'borrowers', 'statistics', 'items', 'biblioitems' ], |
| 96 |
CAT => [ 'items', 'biblioitems', 'biblio' ], |
99 |
CAT => [ 'items', 'biblioitems', 'biblio' ], |
| 97 |
PAT => ['borrowers'], |
100 |
PAT => ['borrowers'], |
| 98 |
ACQ => [ 'aqorders', 'biblio', 'items' ], |
101 |
ACQ => [ 'aqorders', 'biblio', 'items' ], |
| 99 |
ACC => [ 'borrowers', 'accountlines' ], |
102 |
ACC => [ 'borrowers', 'accountlines' ], |
| 100 |
); |
103 |
); |
| 101 |
my %keys = ( |
|
|
| 102 |
CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber', |
| 103 |
'items.itemnumber = statistics.itemnumber', |
| 104 |
'biblioitems.biblioitemnumber = items.biblioitemnumber' ], |
| 105 |
CAT => [ 'items.biblioitemnumber=biblioitems.biblioitemnumber', |
| 106 |
'biblioitems.biblionumber=biblio.biblionumber' ], |
| 107 |
PAT => [], |
| 108 |
ACQ => [ 'aqorders.biblionumber=biblio.biblionumber', |
| 109 |
'biblio.biblionumber=items.biblionumber' ], |
| 110 |
ACC => ['borrowers.borrowernumber=accountlines.borrowernumber'], |
| 111 |
); |
| 112 |
|
| 113 |
# have to do someting here to know if its dropdown, free text, date etc |
| 114 |
my %criteria = ( |
| 115 |
CIRC => [ 'statistics.type', 'borrowers.categorycode', 'statistics.branch', |
| 116 |
'biblioitems.publicationyear|date', 'items.dateaccessioned|date' ], |
| 117 |
CAT => [ 'items.itemnumber|textrange', 'items.biblionumber|textrange', |
| 118 |
'items.barcode|textrange', 'biblio.frameworkcode', |
| 119 |
'items.holdingbranch', 'items.homebranch', |
| 120 |
'biblio.datecreated|daterange', 'biblio.timestamp|daterange', |
| 121 |
'items.onloan|daterange', 'items.ccode', |
| 122 |
'items.itemcallnumber|textrange', 'items.itype', 'items.itemlost', |
| 123 |
'items.location' ], |
| 124 |
PAT => [ 'borrowers.branchcode', 'borrowers.categorycode' ], |
| 125 |
ACQ => ['aqorders.datereceived|date'], |
| 126 |
ACC => [ 'borrowers.branchcode', 'borrowers.categorycode' ], |
| 127 |
); |
| 128 |
|
| 129 |
# Adds itemtypes to criteria, according to the syspref |
| 130 |
if ( C4::Context->preference('item-level_itypes') ) { |
| 131 |
unshift @{ $criteria{'CIRC'} }, 'items.itype'; |
| 132 |
unshift @{ $criteria{'CAT'} }, 'items.itype'; |
| 133 |
} else { |
| 134 |
unshift @{ $criteria{'CIRC'} }, 'biblioitems.itemtype'; |
| 135 |
unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype'; |
| 136 |
} |
104 |
} |
| 137 |
|
105 |
|
| 138 |
=head2 get_report_types |
106 |
=head2 get_report_types |
|
Lines 216-221
sub get_columns {
Link Here
|
| 216 |
|
184 |
|
| 217 |
# this calls the internal fucntion _get_columns |
185 |
# this calls the internal fucntion _get_columns |
| 218 |
my ( $area, $cgi ) = @_; |
186 |
my ( $area, $cgi ) = @_; |
|
|
187 |
my %table_areas = get_table_areas; |
| 219 |
my $tables = $table_areas{$area} |
188 |
my $tables = $table_areas{$area} |
| 220 |
or die qq{Unsuported report area "$area"}; |
189 |
or die qq{Unsuported report area "$area"}; |
| 221 |
|
190 |
|
|
Lines 260-267
This is what get_columns returns.
Link Here
|
| 260 |
|
229 |
|
| 261 |
sub build_query { |
230 |
sub build_query { |
| 262 |
my ( $columns, $criteria, $orderby, $area, $totals, $definition ) = @_; |
231 |
my ( $columns, $criteria, $orderby, $area, $totals, $definition ) = @_; |
|
|
232 |
|
| 233 |
my %keys = ( |
| 234 |
CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber', |
| 235 |
'items.itemnumber = statistics.itemnumber', |
| 236 |
'biblioitems.biblioitemnumber = items.biblioitemnumber' ], |
| 237 |
CAT => [ 'items.biblioitemnumber=biblioitems.biblioitemnumber', |
| 238 |
'biblioitems.biblionumber=biblio.biblionumber' ], |
| 239 |
PAT => [], |
| 240 |
ACQ => [ 'aqorders.biblionumber=biblio.biblionumber', |
| 241 |
'biblio.biblionumber=items.biblionumber' ], |
| 242 |
ACC => ['borrowers.borrowernumber=accountlines.borrowernumber'], |
| 243 |
); |
| 244 |
|
| 245 |
|
| 263 |
### $orderby |
246 |
### $orderby |
| 264 |
my $keys = $keys{$area}; |
247 |
my $keys = $keys{$area}; |
|
|
248 |
my %table_areas = get_table_areas; |
| 265 |
my $tables = $table_areas{$area}; |
249 |
my $tables = $table_areas{$area}; |
| 266 |
|
250 |
|
| 267 |
my $sql = |
251 |
my $sql = |
|
Lines 331-336
Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the crite
Link Here
|
| 331 |
sub get_criteria { |
315 |
sub get_criteria { |
| 332 |
my ($area,$cgi) = @_; |
316 |
my ($area,$cgi) = @_; |
| 333 |
my $dbh = C4::Context->dbh(); |
317 |
my $dbh = C4::Context->dbh(); |
|
|
318 |
|
| 319 |
# have to do someting here to know if its dropdown, free text, date etc |
| 320 |
my %criteria = ( |
| 321 |
CIRC => [ 'statistics.type', 'borrowers.categorycode', 'statistics.branch', |
| 322 |
'biblioitems.publicationyear|date', 'items.dateaccessioned|date' ], |
| 323 |
CAT => [ 'items.itemnumber|textrange', 'items.biblionumber|textrange', |
| 324 |
'items.barcode|textrange', 'biblio.frameworkcode', |
| 325 |
'items.holdingbranch', 'items.homebranch', |
| 326 |
'biblio.datecreated|daterange', 'biblio.timestamp|daterange', |
| 327 |
'items.onloan|daterange', 'items.ccode', |
| 328 |
'items.itemcallnumber|textrange', 'items.itype', 'items.itemlost', |
| 329 |
'items.location' ], |
| 330 |
PAT => [ 'borrowers.branchcode', 'borrowers.categorycode' ], |
| 331 |
ACQ => ['aqorders.datereceived|date'], |
| 332 |
ACC => [ 'borrowers.branchcode', 'borrowers.categorycode' ], |
| 333 |
); |
| 334 |
|
| 335 |
# Adds itemtypes to criteria, according to the syspref |
| 336 |
if ( C4::Context->preference('item-level_itypes') ) { |
| 337 |
unshift @{ $criteria{'CIRC'} }, 'items.itype'; |
| 338 |
unshift @{ $criteria{'CAT'} }, 'items.itype'; |
| 339 |
} else { |
| 340 |
unshift @{ $criteria{'CIRC'} }, 'biblioitems.itemtype'; |
| 341 |
unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype'; |
| 342 |
} |
| 343 |
|
| 344 |
|
| 334 |
my $crit = $criteria{$area}; |
345 |
my $crit = $criteria{$area}; |
| 335 |
my $column_defs = _get_column_defs($cgi); |
346 |
my $column_defs = _get_column_defs($cgi); |
| 336 |
my @criteria_array; |
347 |
my @criteria_array; |
|
Lines 627-634
sub delete_report {
Link Here
|
| 627 |
return $sth->execute(@ids); |
638 |
return $sth->execute(@ids); |
| 628 |
} |
639 |
} |
| 629 |
|
640 |
|
| 630 |
my $SAVED_REPORTS_BASE_QRY = <<EOQ; |
641 |
sub get_saved_reports_base_query { |
| 631 |
SELECT s.*, r.report, r.date_run, $AREA_NAME_SQL_SNIPPET, av_g.lib AS groupname, av_sg.lib AS subgroupname, |
642 |
|
|
|
643 |
my $area_name_sql_snippet = get_area_name_sql_snippet; |
| 644 |
return <<EOQ; |
| 645 |
SELECT s.*, r.report, r.date_run, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname, |
| 632 |
b.firstname AS borrowerfirstname, b.surname AS borrowersurname |
646 |
b.firstname AS borrowerfirstname, b.surname AS borrowersurname |
| 633 |
FROM saved_sql s |
647 |
FROM saved_sql s |
| 634 |
LEFT JOIN saved_reports r ON r.report_id = s.id |
648 |
LEFT JOIN saved_reports r ON r.report_id = s.id |
|
Lines 636-642
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av
Link Here
|
| 636 |
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup) |
650 |
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup) |
| 637 |
LEFT OUTER JOIN borrowers b USING (borrowernumber) |
651 |
LEFT OUTER JOIN borrowers b USING (borrowernumber) |
| 638 |
EOQ |
652 |
EOQ |
| 639 |
my $DATE_FORMAT = "%d/%m/%Y"; |
653 |
} |
|
|
654 |
|
| 640 |
sub get_saved_reports { |
655 |
sub get_saved_reports { |
| 641 |
# $filter is either { date => $d, author => $a, keyword => $kw, } |
656 |
# $filter is either { date => $d, author => $a, keyword => $kw, } |
| 642 |
# or $keyword. Optional. |
657 |
# or $keyword. Optional. |
|
Lines 645-651
sub get_saved_reports {
Link Here
|
| 645 |
my ($group, $subgroup) = @_; |
660 |
my ($group, $subgroup) = @_; |
| 646 |
|
661 |
|
| 647 |
my $dbh = C4::Context->dbh(); |
662 |
my $dbh = C4::Context->dbh(); |
| 648 |
my $query = $SAVED_REPORTS_BASE_QRY; |
663 |
my $query = get_saved_reports_base_query; |
| 649 |
my (@cond,@args); |
664 |
my (@cond,@args); |
| 650 |
if ($filter) { |
665 |
if ($filter) { |
| 651 |
if (my $date = $filter->{date}) { |
666 |
if (my $date = $filter->{date}) { |
|
Lines 797-810
sub save_dictionary {
Link Here
|
| 797 |
return 1; |
812 |
return 1; |
| 798 |
} |
813 |
} |
| 799 |
|
814 |
|
| 800 |
my $DICTIONARY_BASE_QRY = <<EOQ; |
|
|
| 801 |
SELECT d.*, $AREA_NAME_SQL_SNIPPET |
| 802 |
FROM reports_dictionary d |
| 803 |
EOQ |
| 804 |
sub get_from_dictionary { |
815 |
sub get_from_dictionary { |
| 805 |
my ( $area, $id ) = @_; |
816 |
my ( $area, $id ) = @_; |
| 806 |
my $dbh = C4::Context->dbh(); |
817 |
my $dbh = C4::Context->dbh(); |
| 807 |
my $query = $DICTIONARY_BASE_QRY; |
818 |
my $area_name_sql_snippet = get_area_name_sql_snippet; |
|
|
819 |
my $query = <<EOQ; |
| 820 |
SELECT d.*, $area_name_sql_snippet |
| 821 |
FROM reports_dictionary d |
| 822 |
EOQ |
| 823 |
|
| 808 |
if ($area) { |
824 |
if ($area) { |
| 809 |
$query .= " WHERE report_area = ?"; |
825 |
$query .= " WHERE report_area = ?"; |
| 810 |
} elsif ($id) { |
826 |
} elsif ($id) { |
| 811 |
- |
|
|