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

(-)a/C4/Members/Statistics.pm (-10 / +18 lines)
Lines 42-47 BEGIN { Link Here
42
42
43
43
44
our $fields = get_fields();
44
our $fields = get_fields();
45
our $tmpSelectAlias = 'tmp';
45
46
46
sub get_fields {
47
sub get_fields {
47
    my $r = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
48
    my $r = C4::Context->preference('StatisticsFields') || 'location|itype|ccode';
Lines 59-72 sub get_fields { Link Here
59
sub construct_query {
60
sub construct_query {
60
    my $count    = shift;
61
    my $count    = shift;
61
    my $subquery = shift;
62
    my $subquery = shift;
62
    my @select_fields = split '\|', $fields;
63
64
    my @select_fields;
65
    #Some DB engines and/or MySQL variants cannot cope with return-keyword, thus it needs to be prefixed.
66
    if ($subquery =~ /$tmpSelectAlias$/) {
67
        @select_fields = map {"$tmpSelectAlias.$_"} split('\|', $fields);
68
    }
69
    else {
70
        @select_fields = map {($_ =~ /return/) ? "i.$_" : $_} split('\|', $fields);
71
    }
72
63
    my $query = "SELECT COUNT(*) as count_$count,";
73
    my $query = "SELECT COUNT(*) as count_$count,";
64
    $query .= join ',', @select_fields;
74
    $query .= join ',', @select_fields;
65
75
66
    $query .= " " . $subquery;
76
    $query .= " " . $subquery;
67
77
68
    $fields =~ s/\|/,/g;
78
    $query .= " GROUP BY ".join(',', @select_fields);
69
    $query .= " GROUP BY $fields;";
70
79
71
    return $query;
80
    return $query;
72
81
Lines 81-90 sub GetTotalIssuesTodayByBorrower { Link Here
81
90
82
    my $query = construct_query "total_issues_today",
91
    my $query = construct_query "total_issues_today",
83
        "FROM (
92
        "FROM (
84
            SELECT it.* FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
93
            SELECT it.*, i.return FROM issues i, items it WHERE i.itemnumber = it.itemnumber AND i.borrowernumber = ? AND DATE(i.issuedate) = CAST(now() AS date)
85
            UNION
94
            UNION
86
            SELECT it.* FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
95
            SELECT it.*, oi.return FROM old_issues oi, items it WHERE oi.itemnumber = it.itemnumber AND oi.borrowernumber = ? AND DATE(oi.issuedate) = CAST(now() AS date)
87
        ) tmp";     # alias is required by MySQL
96
        ) $tmpSelectAlias";     # alias is required by MySQL
88
97
89
    my $sth = $dbh->prepare($query);
98
    my $sth = $dbh->prepare($query);
90
    $sth->execute($borrowernumber, $borrowernumber);
99
    $sth->execute($borrowernumber, $borrowernumber);
Lines 114-123 sub GetPrecedentStateByBorrower { Link Here
114
123
115
    my $query = construct_query "precedent_state",
124
    my $query = construct_query "precedent_state",
116
        "FROM (
125
        "FROM (
117
            SELECT it.* FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
126
            SELECT it.*, i.return FROM issues i, items it WHERE i.borrowernumber = ? AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date)
118
            UNION
127
            UNION
119
            SELECT it.* FROM old_issues oi, items it WHERE oi.borrowernumber = ? AND oi.itemnumber = it.itemnumber AND DATE(oi.issuedate) < CAST(now() AS date) AND DATE(oi.returndate) = CAST(now() AS date)
128
            SELECT it.*, oi.return FROM old_issues oi, items it WHERE oi.borrowernumber = ? AND oi.itemnumber = it.itemnumber AND DATE(oi.issuedate) < CAST(now() AS date) AND DATE(oi.returndate) = CAST(now() AS date)
120
        ) tmp";     # alias is required by MySQL
129
        ) $tmpSelectAlias";     # alias is required by MySQL
121
130
122
    my $sth = $dbh->prepare($query);
131
    my $sth = $dbh->prepare($query);
123
    $sth->execute($borrowernumber, $borrowernumber);
132
    $sth->execute($borrowernumber, $borrowernumber);
124
- 

Return to bug 14704