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

(-)a/C4/Reports/Guided.pm (-56 / +71 lines)
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
- 

Return to bug 10126