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