[FIX] hr_timesheet_sheet: timesheet report totals

This rev. is related to rev. 89093a2

If several account analytic lines where defined for a same date
within a same sheet, the report totals were multiplied by
the number of occurences of lines of the same day.

Before the above rev., only `Diff`, `Total`, `Timesheet`, were wrong
After, other totals were wrong as well.

The point of the above rev. was to gain a real performance improvment
when there was a lot of timesheets / analytic lines entered
in database. This rev. keeps this performance improvment.

opw-629857

1 files changed, 24 insertions, 5 deletions

diff --git a/addons/hr_timesheet_sheet/report/hr_timesheet_report.py b/addons/hr_timesheet_sheet/report/hr_timesheet_report.py
index e5acede5f88..d3e0ea59957 100644
--- a/addons/hr_timesheet_sheet/report/hr_timesheet_report.py
+++ b/addons/hr_timesheet_sheet/report/hr_timesheet_report.py
@@ -41,20 +41,39 @@ class hr_timesheet_report(osv.osv):
         }
 
     def _select(self):
-        return super(hr_timesheet_report, self)._select() + """,
+        return """
+        WITH
+            totals AS (
+                SELECT
+                    d.sheet_id,
+                    d.name as date,
+                    sum(total_difference) / coalesce(sum(j.count),1) as total_diff,
+                    sum(total_timesheet) / coalesce(sum(j.count),1) as total_timesheet,
+                    sum(total_attendance) / coalesce(sum(j.count),1) as total_attendance
+                FROM hr_timesheet_sheet_sheet_day d left join (
+                    SELECT
+                        h.sheet_id,
+                        a.date,
+                        count(*)
+                    FROM account_analytic_line a inner join  hr_analytic_timesheet h ON (h.line_id=a.id)
+                    GROUP BY h.sheet_id, a.date
+                ) j ON (d.sheet_id = j.sheet_id AND d.name = j.date)
+                GROUP BY d.sheet_id, d.name
+            )
+        """ + super(hr_timesheet_report, self)._select() + """,
                         htss.name,
                         htss.date_from,
                         htss.date_to,
                         count(*) as nbr,
-                        sum(day.total_difference) as total_diff,
-                        sum(day.total_timesheet) as total_timesheet,
-                        sum(day.total_attendance) as total_attendance,
+                        sum(t.total_diff) as total_diff,
+                        sum(t.total_timesheet) as total_timesheet,
+                        sum(t.total_attendance) as total_attendance,
                         aal.to_invoice,
                         htss.department_id,
                         htss.state"""
 
     def _from(self):
-        return super(hr_timesheet_report, self)._from() + "left join hr_timesheet_sheet_sheet as htss ON (hat.sheet_id=htss.id) left join hr_timesheet_sheet_sheet_day AS day ON (htss.id = day.sheet_id)"
+        return super(hr_timesheet_report, self)._from() + "left join hr_timesheet_sheet_sheet as htss ON (hat.sheet_id=htss.id) join totals as t on (t.sheet_id = hat.sheet_id and t.date = aal.date)"
 
     def _group_by(self):
         return super(hr_timesheet_report, self)._group_by() + """,